CIS 308 Exam 2 SG

HLOOKUP function

Combine the above but in a fairly complicated way, Returning the value in a specified row within a two dimensional data area, Lookup data must be in the top row of the array, and Alphabetical order

Copying Validation

Copy what you need→Select cells you want to paste→right click on them→paste special→Paste heading→ validation

MATCH function

Finds the row or column in which a value is stored in a one dimensional way

How to Perform Iterations automatically

Formulas→Automatic→turn off enable iterative calculations→Developer

Axis Titles

Give you understanding of the data of what the chart is all about

IF Functions

Gives alternative results depending on a condition

Trendline

Graphically display the trends in data and to analyze the problems of prediction (straight line)

Error Bars

Graphically express the potential error amounts relative to each data marker in a data series. For example, you can show a 5% positive or negative potential error amounts in the results of a scientific experiment

To create a third dimensional table

Having a drop down list which you select the third variable

Identifying Cells with Validations

Home→find & select→data validation

Circular reference

If you write a formula and refer to the cell that you're writing the formula in you created this

Lists and Sublists

Indirect function: value selected from one list can be used to set the values available in another list

Charts

Insert→Insert Column or Bar Chart

How to Scroll Bar Control

Insert→Scroll Bar ActiveXControl→Properties→LinkedCell

chart animation

Iteration shows up best when the values it generates are plotted on a chart

Data Labels

Make chart easier to understand because they show the details about a data series or its individual data points

Scroll Bar Control

Offers the same features as the spinner, plus more

Add a date

=CONCATENATE("Today is ", TEXT(TODAY(), "dd-mmm-yy")) (would be 14-Jul-15)

With a Space

=CONCATENATE(A2, " ", B2)

Add words

=CONCATENATE(A2, " ", B2, "completed")

No space

=CONCATENATE(A2, B2)

How to VLOOKUP function

Type =Vlookup to get the function→click the cell in which the information you are looking up is→Select the array→Use MATCH to find the column you want to return→click on the cell you are trying to match→Select the header records→asks what kind of match you want, choose 0 if you want an exact match

Creating your own functions

Visual basic editor -Excel→Preferences→Ribbon & Toolbar→Customize ribbon→Developer→Visual Basic→Insert→Module→Type function (ex: function myround(x)) (defines the function with its name and its arguments))→Type required calculation using the function name you already defined on the left hand side to store the result (ex: myround = rnd() * x)

Example of VLOOKUP

Vlookup(B36,A3:L16,MATCH(B35,A3:L3,0)) -2015 -Select whole table/array with headers included -Match > "atlantic total" -Select headers only -0

Combo Boxes

these are the same as List Boxes except that the list is hidden by default (as such, they take up less space). You only open up the list when you want to select the required value

Toggle Buttons, Check Boxes, and Option Buttons

these can be used to switch a value in a cell between TRUE and FALSE and could also be used to reset the starting values of an iteration, for example.

List Boxes

these display a list of possible values, from which you choose to set a value in a cell (if the list is longer than the drawn box, a scroll bar appears).

Goal Seek Example

x2-2x-3→ =A2*A2-2*A2-3

If your Significance F value is Higher than 0.05

you need to select a new set of independent variables.

If your Significance F value is below 0.05

your regression is okay.

Data Series

-A collection of data points and normally corresponds to the data within a single row or column -Could be columns, bars, or a series of squares/crosses joined together by a line

R Square

-A measure, statistically, of how close your data is to the regression line. -It is also called the coefficient of determination. -The closer the R Square is to 1, the better the regression fits the data.

editing a macro (a loop)

-Always add before the ActiveCell.Offset (for k=x to y) -Add before end sub (next k)

Legend

-Appears by default (like a key bank) -Can be hidden

Plot Area

-Area which is enclosed by the two axis -Does Not include titles, etc

To create a second dimensional table

-Column field as well as a row field -Add a Category field to the columns area

How to Solver

-Data→Analysis→Solver→Set Objective (must contain a formula) -To Make the value of the objective cell as large as possible, click Max -To Make the value of the objective cell as small as possible, click Min -To Set the objective cell to a certain value, Click Value Of, then type the value in the box -By changing variable cells box→enter a name or reference for each decision variable cell range -Subject to constraints box→add any constraints you want to apply

Chart Area

-Everything within the outside border -The area that makes up the chart

How to IF functions

-IF (logical-expression) THEN statements-1 ELSE statements-2 END IF -where statements-1 and statements-2 are sequences of executable statements, and logical-expression is a logical expression. The execution of this IF-THEN-ELSE-END IF statement goes as follows: -the logical-expression is evaluated, yielding a logical value -if the result is .TRUE., the statements in statements-1 are executed -if the result is .FALSE., the statements in statements-2 are executed -after finish executing statements in statements-1 or statements-2, the statement following END IF is executed.

Creating a pivot table

-Insert→PivotTable -Two pivot table tabs (analyze & design) appear -Specify which data series you are using in your cross-tabulation by moving fields from the Pivottable fields to the areas below that are Filters. Columns, Rows, Values -Numeric things are applied to values= SUM function -Text fields= COUNT function

Solver

-Multiple cells (storing and/or calculating values) -Have the ability to set up constraint criteria in cells -Find an optimal (max/min) value for a formula in one cell (objective cell -Works with a group of cells called decision variables/variable cells that are used in decision variable cells to satisfy the limits on constraints cells and produce the result you want for the objective cell -Determine max or min value of one cell by changing other cells

Regression

-Statistical measure used to determine the relationship between one dependent variable and a series of other, changing independent variables that you suggest may have an impact on the dependent variable (y) -Determines which of thoise changing variables do have a direct impact

Iteration

-The process of telling Excel that a value in a cell increases/decreases by a number -This process continues until a preset limit/criteria is reached -Solve optimization problems

Goal Seek

-Used for simple problems -Two cells normally- Target and a changing cell -Allows you to see how one data item in a formula impacts another (cause and effect scenario)

How to Validations

1. Data validation→Allow List→Highlight list 2. Hide list (if you want) a. Option A: Hide column b. Option B: Highlight all cells→ format cells→fill all cells white so all lines go away→ insert text box on list→ format shape so there is no line around it

Macros

A series of instructions that Excel follows from a single command

VLOOKUP function

Combine the above but in a fairly complicated way, Returning the value in a specified column within a two dimensional data area, Lookup data must be in the top column of the array, and Alphabetical order

Spin Button controls

Can be used to change the value in a specific cell between a max and min value

Outlines

Can be used to hide data and show only the subtotal or individual data you want to display

Axes

Charts typically have two axes that are used to measure and categorize the data -Vertical axis- y axis -Horizontal axis- x axis

How to Warning messages

Data tab→Data Validation→Input Message

Subtotals

Data tab→subtotal

How to Goal Seek

Data→What-if analysis→Goal Seek

How to Regression

Data→data analysis→Regression

how to relative reference

Developer→Record Macro→SAME INSTRUCTIONS AS THE ABSOLUTE→Click Relative References before starting→SAME INSTRUCTIONS AS THE ABSOLUTE

how to absolute references

Developer→Record Macro→Set Macro Name→add a shortcut (ex:Ctrl + q)→Set Store macro→add description→OK to start recording→Excel now begins recording each keystroke you make. A recording button appears on the bottom frame→Stop recording, hit the little box→Test macro, press your your ctrl + to run the macro

Warning Messages

Excel provides three different levels of error alert a. Stop: will only accept valid data b. Warning: allows the user to enter invalid data if they click a button c. Information: accepts invalid data

How to Iteration

Excel→Calculations→change the workbook calculator to manual→enable iterative calculation

Custom List

Excel→Preferences→Custom Lists→Advanced→Edit Custom Lists→Type in List entries

Pivot tables can...

Pivot table does not automatically update if data changes

Validations

Restricts data entry to cells to numbers, dates, or values from a list, then let's you validate the information being entered to ensure it matches the restrictions set

INDEX function

Returns the value by specified row plus column reference

Left to right sorting

Select data→Go to where you want to paste→Paste special→Transpose→Sort & filter→Under orientation in sort options window, select Sort left to right (you can choose what rows too)

Sorting

Sort & Filter→Choose how you want to sort

Advanced Sorting

Sort & Filter→Custom Sort→Choose what you will sort by (initial sort)→Choose Order→Add levels to add another sorting level

Pivot tables can...

Sort by sum of in value filters

How to Spin Button controls

Spinner→Properties/Format control→Set LinkedCell

Coefficients

The regression line is calculated using the coefficients

Residuals

These numbers show you how far away your actual data points are from the predicted data points

Chart Title

Title box appears above the chart

Significance Values

To measure the statistical significance of your regression results, you want to look at the Significance F value and your P values

Absolute Reference

To perform instructions on specific cells, such as moving to a specific cell, calculating the sum total of the specific cells above that cell and formatting the result output as an Accounting format.

relative reference

To perform instructions, such as moving to a cell a number of spaces away from your current position

Editing a macro

When you edit a macro you are taken into the Visual Basic Editor, and you need to understand exactly what the programming code is doing. Even if you don't know the entire Visual Basic programming language, you can make changes and turn simple recorded macros into more useful ones.

The macro can be run, or started, in various different ways

You can set it to run from the keyboard by giving it a control-plus key combination; by inserting a button on the Quick Access toolbar (similar to how we input the Form button) and setting the macro code behind it; or through the menu system