Basic Excel
Every function, formula, and calculation in Excel begins with this
=
The ______ function is used to calculate the regularly occurring loan payment amount.
PMT(rate, nper, pv, [fv], [type])
What function adds all the numbers in a range of cells based on a single or multiple criteria?
SUMIFS
Using Nested IF functions, how many IF functions would you need if there are five outcomes?
Four
# of IF functions = # of outcomes - 1
If you want to make a cell appear blank, what do you type as the custom format?
;;;
A(n) _________ cell reference remains constant even if the reference is copied or moved to another cell or worksheet.
Absolute
The ______ function calculates the current value of either lump sum investments or investments involving equal periodic payments.
PV(rate, nper, pmt, [fv], [type])
What function would you use to find the birthday of the oldest person?
MIN
What function gives you the position of one cell in a column or a row of cells?
MATCH
If we were creating a two-variable data table to show how different interest rates and retirement ages would affect how much we would have at retirement, what would be referenced in the upper left corner cell of the data table?
The cell in the model that has the calculation for how much we would have at retirement.
How do you center content across a range of cells without merging the cells? Must include all steps.
Center Across Selection (Select cells, Right-Click, format cells, Alignment tab, Center Across Selection)
What function would you use to calculate the total number of periods in an investment or loan?
NPER(rate, pmt, pv, [fv], [type])
The helper function used in the logical test of an IF function when there is more than one test that has to be true in order to get the value_if_true outcome.
AND
What function gives you the position of a single character within a string of text?
FIND or SEARCH
Numeric fields are often added to which area in the PivotTable Fields Pane?
Values
What do you use so that all numbers less than 0 have a dark red fill and bold, white font?
Conditional Formatting
Found only in PPMT and IPMT functions, what does the per argument mean?
The current period or payment
In SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS, explain what criteria gets quoted and what criteria does not get quoted.
Numbers do not get quoted: 100
Everything else gets quoted, and the entire criteria has quotes around it: "User" ">=10"
What is the last argument in the VLOOKUP and HLOOKUP functions?
TRUE - approximate match
FALSE - exact match
For a vertical axis in a chart, if you need to change what number it starts with and what number it ends with, what Axis Options would you change?
The Minimum and Maximum Bounds
What function, that does not have the word "round" in it, rounds down to the nearest whole number?
INT()
In loan amortization, when calculating a payment period's ending balance, what do you do?
Beginning Balance - Principal Payment
What are the three arguments, in order, of the DATEDIF function?
Start_date or "earlier date"
End_date or "later date"
Unit or "Y," "M," or "D"
The function(s) needed in order to return the first name from the text string "DeMoss, Megan" Must name all the functions needed, in the order of their use.
RIGHT, LEN, FIND
=RIGHT(A1, LEN(A1)-FIND(",", A1)-1)
How do you format the values in a PivotTable? Must include all the steps.
Value Field Settings (Click on the field name in the PivotTable Fields Pane and then click on Value Field Settings> Number Format)