Deck 2: Formulas and Functions

Question 1

If a cell contains =$N$21 and two new columns are inserted in front of column N and three rows are inserted above row 21,what happens to the =$N$21?

A)It becomes =$P$24
B)It remains =$N$21
C)It becomes =$L$18
D)It becomes =$Q$23

Answer 1

A

Question 2

Which keyboard shortcut toggles through the types of reference options?

A)F2
B)F3
C)F4
D)F5

Answer 2

C

Question 3

If a cell containing =B$14 is copy and pasted three columns over and two rows down,what would the resultant cell reference become?

A)E$14
B)$E$14
C)B16
D)B$16

Answer 3

A

Question 4

What does Excel use to indicate optional arguments in a function?

A)Square brackets []
B)Parenthesis ()
C)Curly brackets {}
D)Quote marks ""

Question 5

What error will you get if you type a function name incorrectly?

A)#FUNCTION?
B)#VALUE?
C)#ERROR?
D)#NAME?

Question 6

What do you use to separate arguments in a function?

A)Period (. )
B)Semi-colon (;)
C)Comma (,)
D)Colon (:)

Question 7

Which function would you use if you wanted to count the number of values,but ignore cells that have text or are empty?

A)COUNT
B)COUNTA
C)COUNTBLANK
D)COUNTVALUES

Question 8

Functions can be selected by using the ________.

A)Add Function dialog box
B)Format Function dialog box
C)Insert Function dialog box
D)Create Function dialog box

Question 9

How many functions does Excel provide?

A)Over 40
B)Over 400
C)Over 4,000
D)Unlimited

Question 10

If the cell reference $A5 in a formula is copied and pasted over two columns and down two rows from its original cell,what the cell reference become in the new cell.

A)$A5
B)$A7
C)$C5
D)$C7

Question 11

What type of cell reference should be used when a value remains constant?

A)Absolute
B)Relative
C)Mixed
D)Constant

Question 12

Where does Excel automatically display statistics like count,average,and sum when a range of values is selected?

A)Formula bar
B)Name box
C)Status bar
D)Statistics bar

Question 13

Which of the following is not a category of functions?

A)Logical
B)Financial
C)Statistical
D)Graphing

Question 14

What two functions compute the central tendency of values?

A)AVERAGE and MODE
B)AVERAGE and MEDIAN
C)MIN and MAX
D)AVERAGE and CENTER

Question 15

What punctuation is used to separate the two cell references in a range?

A)Period (. )
B)Semi-colon (;)
C)Comma (,)
D)Colon (:)

Question 16

If a cell containing the formula =$B$14 is not copy and pasted,but moved four columns over and four rows down,what would the resultant cell reference become?

A)=F18
B)=$F$18
C)=B14
D)=$B$14

Question 17

If you wanted to reference a cell in a formula and you did not want that cell to change when copying and pasting you would use what kind of reference?

A)Fixed
B)Relative
C)Mixed
D)Absolute

Question 18

What is the default method of referencing cells in formulas?

A)Absolute
B)Relative
C)Mixed
D)Default

Question 19

What is not a direct option on the AutoSum drop down menu?

A)Median
B)Sum
C)Count numbers
D)Max

Question 20

What describes a function's purpose?

A)Arguments
B)Name
C)Category
D)Variables

Question 21

Which of the following would not give the same result for "=(5+32+75+21)/4"?

A)=SUM(5,32,75,21)/4
B)=MEDIAN(5,32,75,21)+7.25
C)=AVERAGEIF(5+32+75+21,4)
D)=AVERAGE(5,32,75,21)

Question 22

If A7=21,which of the following =IF statements would produce the result of "Much Greater"?

A)=IF(A7>14,"Greater",IF(A7>20,"Much Greater","Not Greater than 20"))
B)=IF(A7<50,"Greater",IF(A7>20,"Much Greater","Not Greater than 20"))
C)=IF(A7<14,"Greater",IF(A7>20,"Much Greater","Not Greater than 20"))
D)=IF(A7<14,"Greater",IF(A7>20,"Much Greater","Not Greater than 20")

Question 23

What keyboard shortcut will automatically update any formulas using =TODAY()or =NOW()?

A)F8
B)F9
C)F10
D)F11

Question 24

To look up an exact match,what should you enter in the range_lookup argument?

A)FALSE
B)TRUE
C)EXACT
D)Leave it blank

Question 25

The ROUND function requires how many arguments?

A)2
B)3
C)4
D)1

Question 26

Which of the following will not return the system clock's date?

A)NOW()
B)TODAY()
C)Ctrl+;
D)DATE()

Question 27

What actually changes the value Excel stores to a user determined number of decimal places?

A)Decrease decimal setting
B)Increase decimal setting
C)ROUND function
D)DECIMAL function

Question 28

Which status bar statistic can you not enable?

A)SUM
B)MIN
C)Range Name
D)Page Number

Question 29

Which of the following is not a valid test in an =IF statement?

A)A4-B4
B)A4C)A4>A4
D)B4<=B4

Question 30

Which of the following would not produce the correct monthly payment on a loan of $13,000 which will be paid off in 5 years with an annual interest rate of 6%?

A)=PMT(6%,60,-13000)
B)=PMT(.5%,60,-13000)
C)=PMT(.5%,5*12,-13000)
D)=PMT(6%/12,60,-13000)

Question 31

Which of the following is not a valid =IF statement?

A)=IF(A5>19,"Greater","Not Greater")
B)=IF(A5>19,b7,b9)
C)=IF(A5>19,1,9)
D)=IF(A5>19,Greater,Not Greater)

Question 32

Which of the following does not comply with the preferred way of using the SUM function?

A)=SUM(A4:A11)
B)=SUM(A4:A11,B9:E11)
C)=SUM(A4,B8,D14)
D)=SUM(A4/A11)

Question 33

Which of the following is not a valid lookup function?

A)HLOOKUP
B)VLOOKUP
C)LLOOKUP
D)LOOKUP

Question 34

What does the AVERAGE function not ignore?

A)Text
B)Empty cells
C)Dates
D)Cells with N/A

Question 35

Which function would you use to find the oldest date in a range?

A)MIN
B)MAX
C)OLDEST
D)MEDIAN

Question 36

What function computes the value in which one-half of the data is above and one-half is below.

A)MIDDLE
B)MODE
C)AVERAGE
D)MEDIAN

Question 37

If you wanted to count the number of entries in a list that contained numbers,text,and blank cells,but ignore the text and blank cells,which function would you use?

A)COUNT
B)COUNTA
C)COUNTBLANK
D)COUNTIF

Question 38

If you wanted to count the number of entries in a list that contained numbers,text,and blank cells,but ignore the blank cells,which function would you use?

A)COUNT
B)COUNTA
C)COUNTBLANK
D)COUNTIF

Question 39

What will you see if you enter the TODAY function without the parenthesis?

A)#VALUE?
B)Nothing.It will fill in the parenthesis for you.
C)#FUNCTION?
D)#NAME?

Question 40

Which of the following is not an argument associated with the IF function?

A)Test
B)Value if true
C)Value if false
D)Value if neither true nor false

Question 41

The test argument in an IF statement must evaluate to either ________.

Question 42

The MIN and MAX functions can be used with ________ as well as numbers.

Question 43

The TODAY()and NOW()functions are updated every time you open or ________ the spreadsheet.

Question 44

________ is a set of analytical tools that appears when you select a range.

Question 45

________ references change when formulas are copy and pasted.

Question 46

A function's ________ defines the rules by which the function operates.

Question 47

When working with dates,Excel actually assigns ________ numbers to each date.

Question 48

The ________ function is similar to the TODAY()function,except that it also returns the system time.

Question 49

The ________ function counts the number of calculable values,but ignores cells that have text or are empty.

Question 50

If A9>B7 is the test in an IF function,________ would result in the same results as long as the value if true and the value if false arguments are switched.

Question 51

Using ________ simplifies adding complex mathematical formulas to a cell.

Question 52

The ________ feature in Excel displays various options when you type an equal (=)sign and then begin typing a function name.

Question 53

When you use the VLOOKUP function,the ________ column in the array is used to look up the value.

Question 54

The ________ function finds the smallest value in a range.

Question 55

The ________ function finds the largest value in a range.

Question 56

In military time,15:
00 is ________ in non-military time.

Question 57

The range that contains the lookup table is called the ________.

Question 58

It is called a(n)________ function when a function is used inside of another function.

Question 59

The inputs in a function are specified by its ________.

Question 60

After you type a function and its opening parenthesis "(" the ________ appears and displays the function's arguments.

Question 61

The COUNTBLANK function will count cells that contain formulas that compute to a value of zero (0).

Question 62

You could use both A3>b9 or B9

Question 63

If you will be paying monthly for six years to pay off a car,then you would enter ________ in the NPER argument in the PMT function.

Question 64

There are rare occasions when both the second and third argument of an IF function are executed.

Question 65

If you will never copy and paste a formula you do not have to be concerned about absolute,relative,or mixed references.

Question 66

In a mixed reference you can put the dollar sign ($)in front of the column or row indicator.

Question 67

It is very important to sort the first column in a table lookup array in descending order.

Question 68

The COUNTA function only counts text entries.It does not count calculable values or blank cells.

Question 69

In a lookup table,the number of the column which contains the return values is called the ________.

Question 70

When entering functions,the square brackets,[],indicate required values.

Question 71

If the annual interest rate is 12% and payments are made monthly,then you would enter ________ in the RATE argument in the PMT function.

Question 72

A good practice when entering function names is to type them in lowercase and see if Excel converts them to upper case.

Question 73

The ________ is the lowest value for a category in the table lookup table.

Question 74

The most used logical function is the IF function.

Question 75

The test in an IF function must evaluate to either a True or a False.

Question 76

Excel assigns date number 1 to January 1,1900.

Question 77

In the PMT function,the PV argument refers to the ________ of the loan.

Question 78

This is not a valid way to use the MAX function:
=MAX(a4:
d11,f1:
f2)

Question 79

All IF functions have three arguments.

Question 80

The VLOOKUP function has four required arguments.