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