Poatsy 1st Edition Test Questions & Answers

Exploring Microsoft Office 2019 Introductory (Poatsy/Grauer)

Excel Chapter 2 Formulas and Functions

1) 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

Diff: 1

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

2) If a cell containing =B$14 is copied 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

Diff: 2

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

3) 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

Diff: 2

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

4) What is the default method of referencing cells in formulas?

A) Absolute

B) Relative

C) Mixed

D) Default

Diff: 2

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

5) Which of the following is not a valid logical test?

A) =>

B) <=

C) <>

D) >=

Diff: 2

Objective: E2.07 Use the IF Function

6) Which keyboard shortcut toggles through the types of reference options?

A) F2

B) F3

C) F4

D) F5

Diff: 3

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

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

A) Absolute

B) Relative

C) Mixed

D) Constant

Diff: 2

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

8) 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

Diff: 2

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

9) Which Excel function changes the value to a desired number of decimal places?

A) truncate

B) Round

C) Decrease decimal

D) Increase decimal

Diff: 3

Objective: E2.03 Insert Basic Math and Statistics Functions

10) What describes a function's purpose?

A) Arguments

B) Name

C) Category

D) Variables

Diff: 2

Objective: E2.02 Insert a Function

11) Which of the following is not a category of functions?

A) Logical

B) Financial

C) Statistical

D) Graphing

Diff: 2

Objective: E2.02 Insert a Function

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

A) Period (.)

B) Semi-colon (;)

C) Comma (,)

D) Colon (:)

Diff: 1

Objective: E2.02 Insert a Function

13) 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

Diff: 2

Objective: E2.02 Insert a Function

14) What do you use to separate arguments in a function?

A) Period (.)

B) Semi-colon (;)

C) Comma (,)

D) Colon (:)

Diff: 1

Objective: E2.02 Insert a Function

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

A) #FUNCTION?

B) #VALUE?

C) #ERROR?

D) #NAME?

Diff: 3

Objective: E2.02 Insert a Function

16) What term refers to the necessity of following the rules when it comes to using functions?

A) syntax

B) formula

C) arguments

D) function logic

Diff: 1

Objective: E2.02 Insert a Function

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

A) Square brackets []

B) Parenthesis ()

C) Curly brackets {}

D) Quote marks ""

Diff: 2

Objective: E2.02 Insert a Function

18) 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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

19) 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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

20) 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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

21) The ROUND function requires how many arguments?

A) 2

B) 3

C) 4

D) 1

Diff: 3

Objective: E2.03 Insert Basic Math and Statistics Functions

22) What is the term for the inputs required in a function?

A) formulas

B) syntax

C) arguments

D) terms

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

23) 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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

24) Which status bar statistic can you not enable?

A) SUM

B) MIN

C) Range Name

D) Page Number

Diff: 3

Objective: E2.03 Insert Basic Math and Statistics Functions

25) What action changes the actual number of decimal places Excel stores instead of just changing what is displayed?

A) Decrease decimal setting

B) Increase decimal setting

C) ROUND function

D) DECIMAL function

Diff: 3

Objective: E2.03 Insert Basic Math and Statistics Functions

26) What does the AVERAGE function not ignore?

A) Text

B) Empty cells

C) Dates

D) Cells with N/A

Diff: 3

Objective: E2.03 Insert Basic Math and Statistics Functions

27) 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

Diff: 3

Objective: E2.03 Insert Basic Math and Statistics Functions

28) 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)

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

29) 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?

Diff: 3

Objective: E2.04 Use Date Functions

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

A) MIN

B) MAX

C) OLDEST

D) MEDIAN

Diff: 1

Objective: E2.04 Use Date Functions

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

A) NOW()

B) TODAY()

C) Ctrl+;

D) DATE()

Diff: 3

Objective: E2.04 Use Date Functions

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

A) F8

B) F9

C) F10

D) F11

Diff: 3

Objective: E2.04 Use Date Functions

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

A) A4-B4

B) A4<b4

C) A4>A4

D) B4<=B4

Diff: 1

Objective: E2.07 Use the IF Function

34) 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")

Diff: 3

Objective: E2.07 Use the IF Function

35) 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

Diff: 2

Objective: E2.07 Use the IF Function

36) 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)

Diff: 2

Objective: E2.07 Use the IF Function

37) Which of the following is not a valid lookup function?

A) HLOOKUP

B) VLOOKUP

C) LLOOKUP

D) LOOKUP

Diff: 2

Objective: E2.05 Use Lookup Functions

38) 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

Diff: 3

Objective: E2.05 Use Lookup Functions

39) 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)

Diff: 2

Objective: E2.06 Use the PMT Function

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

Diff: 2

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

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

Diff: 2

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

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

Diff: 3

Objective: E2.02 Insert a Function

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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

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

Diff: 3

Objective: E2.03 Insert Basic Math and Statistics Functions

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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

47) Excel assigns date number 1 to January 1, 1900.

Diff: 2

Objective: E2.04 Use Date Functions

48) All IF functions have three arguments.

Diff: 1

Objective: E2.07 Use the IF Function

49) You could use both A3>b9 or B9<A3 as the test in an IF function and get the same results. You would just switch the second and third arguments.

Diff: 3

Objective: E2.07 Use the IF Function

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

Diff: 1

Objective: E2.07 Use the IF Function

51) The most used logical function is the IF function.

Diff: 1

Objective: E2.07 Use the IF Function

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

Diff: 1

Objective: E2.07 Use the IF Function

53) The VLOOKUP function has four required arguments.

Diff: 3

Objective: E2.05 Use Lookup Functions

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

Diff: 2

Objective: E2.05 Use Lookup Functions

55) If you omit the fourth argument in a VLOOKUP function, Excel will assume you intend the fourth argument to be False.

Diff: 3

Objective: E2.05 Use Lookup Functions

56) If you are looking up exact values in a lookup table then it does not matter how the table is sorted.

Diff: 2

Objective: E2.05 Use Lookup Functions

57) In the HLOOKUP function, the third argument is the column index number.

Diff: 3

Objective: E2.05 Use Lookup Functions

58) A lookup table should contain at least two rows and two columns, not counting headings.

Diff: 2

Objective: E2.05 Use Lookup Functions

59) The HLOOKUP function can only be used to find exact matches.

Diff: 3

Objective: E2.05 Use Lookup Functions

60) The PMT function uses three required arguments and no optional arguments.

Diff: 3

Objective: E2.06 Use the PMT Function

61) The default calculation using the PMT function will produce a negative number.

Diff: 2

Objective: E2.06 Use the PMT Function

62) FV is a required argument in the PMT function.

Diff: 3

Objective: E2.06 Use the PMT Function

63) The two optional arguments in the PMT function allows the function to be used with variable loans.

Diff: 3

Objective: E2.06 Use the PMT Function

64) Functions can be nested inside the arguments in the various lookup functions.

Diff: 3

Objective: E2.06 Use the PMT Function

65) ________ references change when formulas are copy and pasted.

Diff: 2

Objective: E2.01 Use Relative, Absolute, and Mixed Cell References in Formulas

66) The inputs in a function are specified by its ________.

Diff: 3

Objective: E2.02 Insert a Function

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

Diff: 3

Objective: E2.02 Insert a Function

68) Using ________ simplifies adding complex mathematical formulas to a cell.

Diff: 1

Objective: E2.02 Insert a Function

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

Diff: 3

Objective: E2.02 Insert a Function

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

Diff: 3

Objective: E2.03 Insert Basic Math and Statistics Functions

71) The ________ function finds the smallest value in a range.

Diff: 1

Objective: E2.03 Insert Basic Math and Statistics Functions

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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

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

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

74) The ________ function finds the largest value in a range.

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

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

Diff: 3

Objective: E2.04 Use Date Functions

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

Diff: 2

Objective: E2.04 Use Date Functions

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

Diff: 2

Objective: E2.04 Use Date Functions

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

Diff: 3

Objective: E2.04 Use Date Functions

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

Diff: 3

Objective: E2.04 Use Date Functions

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

Diff: 1

Objective: E2.07 Use the IF Function

81) The ________ function actually changes the number of decimal places in the value.

Diff: 2

Objective: E2.03 Insert Basic Math and Statistics Functions

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

Diff: 3

Objective: E2.05 Use Lookup Functions

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

Diff: 3

Objective: E2.05 Use Lookup Functions

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

Diff: 3

Objective: E2.05 Use Lookup Functions

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

Diff: 3

Objective: E2.05 Use Lookup Functions

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

Diff: 3

Objective: E2.06 Use the PMT Function

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

Diff: 2

Objective: E2.06 Use the PMT Function

88) 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.

Diff: 3

Objective: E2.06 Use the PMT Function

89) Match the following terms with their description:

I. Absolute cell references

II. Relative cell references

III. Mixed cell references

IV. Argument

V. Function

A. Part of it changes when you copy and paste a formula

B. Changes when you copy and paste a formula

C. Simplifies complex calculations

D. Does not change when you copy and paste a formula

E. Required input in a function

Diff: 1

Objective: Multiple Objectives

90) Match the following terms with their description:

I. Median

II. Average

III. Sum

IV. Table array

V. Breakpoint

A. Total

B. Lowest value in a series

C. Midpoint value

D. Range used for looking up values

E. Arithmetic mean

Diff: 2

Objective: Multiple Objectives

91) Match the following terms with their description:

I. NOW()

II. TODAY()

III. PV

IV. RATE

V. PMT

A. Current amount of a loan

B. Helps figure out how much a loan will cost each month

C. A good example is interest

D. Returns the system date

E. Returns the system date and time

Diff: 2

Objective: Multiple Objectives

92) Match the following terms with their description:

I. COUNT

II. COUNTA

III. COUNTBLANK

IV. IF

V. NPER

A. Only uses calculable values

B. Evaluates true and false statements

C. Includes text, but not blank cells

D. Length of time you will be paying on a loan

E. Only includes empty cells

Diff: 2

Objective: Multiple Objectives

93) Match the following terms with their description:

I. Formula AutoComplete

II. Quick Analysis

III. Function ScreenTip

IV. Logical test

V. Syntax

A. Can be used to apply formatting and insert basic functions

B. Pop-up description

C. A set of rules

D. Matches letters as you type

E. Evaluates to True or False

Diff: 2

Objective: Multiple Objectives

94) Match the example with the function or feature:

I. =A54

II. =$A54

III. =$A$54

IV. =SUM(A54:B97)

V. "Ohio"

A. Function

B. Relative reference

C. Ensures text entry

D. Mixed reference

E. Absolute reference

Diff: 1

Objective: Multiple Objectives

95) Match the example by replacing the ??? with the proper function:

I. =???(B7>9,"Bigger","Smaller")

II. =???(12%/12,360,-120000)

III. =???(A4,B4:F9,2)

IV. =???(B9:E21)

V. =???()

A. NOW

B. PMT

C. VLOOKUP

D. IF

E. SUM

Diff: 3

Objective: Multiple Objectives

96) Match the following terms with their description:

I. VLOOKUP

II. HLOOKUP

III. Lookup table

IV. Lookup value

V. Column index number

A. Data is organized in rows

B. Column that contains the return values

C. Cell that contains the value to be looked up

D. Has the data used with HLOOKUP or VLOOKUP

E. Data is organized in columns

Diff: 2

Objective: E2.05 Use Lookup Functions