Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
1. A constant is a value in a formula that doesn’t change. _______________
a. True
b. False
2. Optional arguments are always placed first in the argument list. _______________
a. True
b. False
3. One challenge of nesting functions is to make sure that you include all of the parentheses. _______________
a. True
b. False
4. Functions are organized in the Function Library group on the FORMULAS tab. _______________
a. True
b. False
5. A mixed reference “locks” one part of the cell reference while the other part can change. _______________
a. True
b. False
6. An error value begins with a pound sign (#) followed by an error name that indicates the type of
error. _______________
a. True
b. False
7. By default, AutoFill copies only the content not the formatting of the original range to the selected
range. _______________
a. True
b. False
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
8. To display the current date and time, which is updated each time the workbook is reopened, use the TODAY
function. _______________
a. True
b. False
9. The IF function is a logical function that returns one value if a condition is true, and returns a different value if that
condition is false. _______________
a. True
b. False
10. Lookup tables can be constructed for exact match or approximate match lookups. _______________
a. True
b. False
11. An argument can be any type of value including text, numbers, cell references, or even other formulas or functions.
_______________
12. An error value indicates that some part of a function is preventing Excel from returning a calculated value.
________________
13. When a formula includes a cell reference, Excel interprets that cell reference as being located relative to the position
of the current cell. _________________
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
14. The following mixed reference in the formula, $A5, has an absolute column reference and a relative row reference.
_________________________
15. The Fill Formatting Only option, which is the default, copies both the content and the formatting. ______________
16. The statement “A1 >= B1” tests whether the value in cell A1 is lesser than or equal to the value in cell B1.
_________________________
17. The WORKDAY function displays the date of the weekday a specific number of weekdays past a starting date.
18. A lookup table organizes numbers or text into categories. _____________
19. A trial-and-error method lets you explore the impact that changing input values has on the calculated values in the
workbook. _________________________
20. Goal Seek automates the trial-and-error process by allowing you to specify a value for a calculated item, which Excel
uses to determine the input value needed to reach that goal. _________________________
21. The ____ category of Excel functions returns (true-false) values.
a. Information b. Statistical
c. Logical d. Math & Trig
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
22. Which of the following is a Statistical function? _____
a. INT b. RAND
c. ROUND d. AVERAGE
23. Which of the following is a Math & Trig function? ____
a. INT b. COUNT
c. MIN d. AVERAGE
24. =ROUND(AVERAGE(A1:A100),2) is an example of a(n) ____ function.
a. complex b. logical
c. absolute d. nested
25. After you describe a function in the Search for a function box in the Insert Function dialog box, click ____.
a. Go b. Select
c. Insert d. Next
26. The numbers, text, or cell references used by the function to return a value are called ____.
a. expressions b. arguments
c. data d. values
27. Which of the following is not a comparison operator? ____
a. = b. <=
c. << d. <>
28. In the following formula: =IF(A1=“YES”, “DONE”, “RESTART”), what happens if A1= “NO”? _____
a. The formula returns the text “RESTART”.
b. The formula returns the text “DONE”.
c. The formula returns the text “NO”.
d. The formula returns the text “NO, RESTART”.
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
29. Which of the following is not a date function? ____
a. YEAR b. NOW
c. AND d. TODAY
30. The ____ function returns the maximum value in the range.
a. TOP b. FIRST
c. MAX d. MOST
31. One of the great advantages of ____ references is that you can quickly generate row and column totals without having
to worry about revising the formulas as you copy them to new locations.
a. cell b. relative
c. absolute d. formula
32. Absolute references are marked with a ____.
a. < b. :
c. $ d. !
33. When you copy a formula that contains an absolute reference to a new location, the reference ____.
a. is updated automatically b. does not change
c. becomes bold d. has a dotted outline in its cell
34. Use ____ references when you want different formulas to refer to the same cell.
a. mixed b. relative
c. absolute d. fixed
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
35. Another name for the middle value is the ____ value.
a. mode b. center
c. median d. relative
36. ____ arguments may provide more control over the returned value.
a. Relative b. Required
c. Optional d. Absolute
37. FUNCTION(argument1, argument2, …) is ____.
a. the syntax of writing optional arguments
b. an example of a formula
c. the general syntax of all functions
d. the general syntax of all formulas
38. If an optional argument is not included, Excel assumes a(n) ____ value for it.
a. default b. generic
c. static d. absolute
39. Excel functions are organized into ____ categories.
a. 8 b. 10
c. 11 d. 13
40. Which of the following is not an Excel function category? ____
a. Math & Trig b. Algebra
c. Statistical d. Text
41. Which function counts how many cells in a range contain numbers, where value1, value2, and so forth are text,
numbers, or cell references?
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
a. SUM b. COUNT
c. COUNTA d. AUTOSUM
42. In “SUM(number1 [, number2, number3, ...])”, number 1, number 2, and number 3 may be numbers or ____.
a. letters b. cell references
c. symbols d. operators
43. Which category of Excel functions returns information about the format, location, or contents of worksheet cells?
____
a. Location b. Information
c. Reference d. Description
44. In the formula =INT(AVERAGE(A1:A100)), which would occur first? ____
a. The average of the values in the range A1:A100 is added to the integer.
b. The average of the integer value is calculated.
c. The average of the values in the range A1:A100 is calculated.
d. The integer portion of the value is extracted.
45. When you nest functions, you should have ____.
a. the equal sign
b. the same number of left and right parentheses
c. at least two functions
d. the correct number of brackets
46. The Insert Function button is in the ____ group.
a. Calculation b. Function Library
c. Sort & Filter d. Home
47. The Insert Function dialog box has a(n) ____ option.
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
a. search b. formatting
c. find and replace d. expand/collapse
48. The fill handle copies ____.
a. content only b. formats only
c. content and formats d. formats and cell references
49. Using the AutoFill Options button, the Fill Without Formatting option copies the ____ but not the formatting.
a. functions b. content
c. cell references d. left-aligns
50. Using AutoFill, what would be the extended series of the initial entry Jan? ___
a. uary b. Feb, Mar, Apr
c. Kan, Lan, Man, Nan d. Jan1, Jan2, Jan3
51. A logical function is a function that works with values that are either ____.
a. right or wrong b. absolute or relative
c. positive or negative d. true or false
52. In the formula =IF(A1=B1, C1, C2), the result will be C2 if ____.
a. A is equal to B b. A does not equal B
c. A1+B1 is equal to C1 d. A1>B1 is equal to C2
53. ____ references are seldom used other than when creating tables of calculated values such as a multiplication table in
which the values of the formula or function can be found in the initial rows and columns of the table.
a. Absolute b. Relative
c. Mixed d. Matched
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
54. =IF(G22=“x”, SUM(H22:J22), “ ”) with display ______ if G22 is not equal to “x”.
a. a blank cell b. the result of the SUM
c. dashes d. False
55. ______ functions find values in tables of data and insert them in another location in the worksheet such as cells or in
formulas.
a. Sum b. Average
c. Lookup d. Find
56. If the VLOOKUP function cannot find the lookup value, the _________ error value is displayed in the cell.
a. #VALUE b. #N/A
c. #ERROR d. #NULL
57. One way to perform a _________ is by changing one or more of the input values to see how they affect the calculated
results.
a. goal seek b. gunction
Case-Based Critical Thinking Questions
Case 3-1
Ellen is a recent college graduate. She has decided to open a T-Shirt business and would like to create budget worksheet
using Microsoft Excel 2016. She need to keep track of her expenses and income for the year.
58. Ellen opens Excel 2016 and enters her monthly income for one year beginning with January. To create a series of
months, she can ____.
a. Type = Jan-Dec in cell A1 and then press Enter.
b. Type January in cell A1 and then drag the filll handle.
c. Type =YEAR in cell A1 and then press Enter.
d. Type =DATE(Jan, Dec) in cell A1 and then press Enter.
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
59. Ellen has entered all of her monthly expenses. She needs to create a formula that will deduct her monthly expenses
from her monthly income. Since her monthly income is the same amount each month, she uses a(n)____ to identify the
cell containing the income value.
a. relative reference b. absolute reference
c. mixed reference d. fixed reference
60. Ellen’s spending varies from month to month. She wants to calculate the total expenses for each month. She wants to
see all of the arguments associated with the SUM function so she clicks the ____ then clicks SUM to open the Function
Arguments dialog box.
a. Math & Trig button in the Formula group
b. Math & Trig button in the Function Library group
c. Financial button in the Formula group
d. Financial button in the Function Library group
61. Ellen wants to find out what her median monthly spending is. The monthly totals for her spending appear in the range
A10:L10. Which of the following is NOT a way to enter the MEDIAN function into her database?
a. Type =MEDIAN(A10:L10) into an empty cell.
b. Select the range A10:L10, click the Quick Analysis button, click TOTALS, and then click Median.
c. Click the More Functions button in the Function Library group on the FORMULAS tab, point to Statistical, and
then click MEDIAN.
d. Click the Insert Function button in the Function Library group on the FORMULAS tab, scroll the Select a
function list, then click MEDIAN.
62. Ellen is expecting to get a 3% raise next year. She enters .03 in cell D2 so that she can predict her monthly income for
the next year. What function key will make D2 an absolute reference?
a. F2 b. F4
c. F6 d. F1
Case-Based Critical Thinking Questions
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
Case 3-2
Susan is in charge of the inventory reports for her company. She will need to find specific inventory items as well as
project increases in inventory costs. To get ready she will create a worksheet and use the Microsoft Excel 2016 Analysis
tools.
63. Susan designs a worksheet with the inventory number, description, quantity, unit cost, reorder point and value. To
find values in tables of data, and insert them in another location in the worksheet such as cells or in formulas, she will use
________.
a. Max function b. Lookup function
c. CountA function d. Sum function
64. Susan needs to use a lookup function to find an exact match of an inventory item, so she will set the range_lookup
value to _______ .
a. EXACT
b. 1
c. TRUE
d. FALSE
65. Susan uses VLOOKUP to retrieve the return value from a vertical lookup table. When she enters the VLOOKUP
function, specifying the lookup value and other criteria, the lookup value cannot be found. It displays ______ error value
in the cell.
a. ##### b. #Value
c. #N/A d. Value not found
66. Susan wants to make some changes to the worksheet to explore the impact that changing input values has on the
calculated values in the worksheet. She can use ______.
a. Lookup Analysis b. What-If Analysis
c. IF conditions d. nested OR conditions
67. Susan wants to automate the trial-and-error process by allowing her to specify a value for a calculated item, which
Excel uses to determine the input value needed to reach that goal. She can use ______.
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
a. Data Validation b. Goal Seek
c. Goal Set d. If Conditional
68. Some cell references are called ____________________ references because they are always interpreted in relation to
the location of the cell containing the formula.
69. ____________________ is a set of rules which specifies how a function should be written.
70. The ____________________ organizes all of the functions by category and allows you to search for functions that
perform particular calculations.
71. A(n) ____________________ function is a function that works with statements that are either true or false.
72. The ____________________ is the middle value from a sample.
73. TODAY is an example of a(n) ____________________ function.
74. A(n) ____________________ reference contains both relative and absolute references.
Name:
Class:
Date:
Excel Module 03: Performing Calculations with Formulas and Functions
75. Not all functions have arguments, and some functions have ____________________ arguments, which are not
required for the function to return a value, but can be included to provide more control over the returned value.