Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
1. All the logical conditions listed in an AND function must be true for the AND function to return a TRUE value.
a. True
b. False
2. If a formula is in a calculated table column, you need to make a change in only one cell and the change will
automatically be copied to all the cells in the column.
a. True
b. False
3. If you need to modify the formula in a calculated column, you edit the formula in one cell of the column and then you
modify the formulas in the remaining cells in that table column.
a. True
b. False
4. A formula that includes a structured reference can be fully qualified or unqualified.
a. True
b. False
5. The ORERROR function can determine if a cell contains an error value and then display the message you choose.
a. True
b. False
6. Nesting IF functions will allow for three or more outcomes.
a. True
b. False
7. Two common categories of formula errors in Excel are syntax errors and logic errors.
a. True
b. False
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
8. The IF function evaluates a(n) single condition. _________________________
9. In a fully qualified structured reference, the table name follows the column qualifier. _________________________
10. In the DATEDIF function, the first date argument is the latest date. _________________________
11. You cannot nest more than one IF function to test for multiple outcomes. _________________________
12. If any logical condition listed in the function is true, the AND function returns a TRUE value.
_________________________
13. Duplicate value highlighting helps to verify that columns of data have unique entries. _________________________
15. An error value begins with a(n) number sign followed by an error name, which indicates the type of error.
_________________________
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
16. You can calculate the number of cells in a range that match criteria you specify using the SUMIF function.
_________________________
17. The VLOOKUP function searches vertically down the first column of the lookup table for the value you entered, and
then retrieves the corresponding value from another column of the table. _________________________
18. When Excel automatically fills the rest of a table column with a formula entered into an empty table column, this is
referred to as a(n) _____ column.
a. concatenated b. calculated
c. autofilled d. custom
19. The _____ function is a logical function that returns a TRUE value if any of the logical conditions are true and a
FALSE value if all the logical conditions are FALSE.
a. IF b. AND
c. OR d. THIS
20. To get the effect of a calculated column in a range of data, you must copy and paste the formula or use the _____
feature.
a. Concatenation b. Merged
c. AutoFill d. Indexed
21. If you edit a cell in a calculated column so it is no longer consistent with the other formulas in the column, a _____
appears in the upper-left corner of the cell.
a. red circle b. blue square
c. red square d. green triangle
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
22. The _____ function is a logical function that evaluates a condition and then returns one value if the condition is true
and another value if the condition is false.
a. THIS b. IF
c. WHEN d. NOT
23. _____ just above the header row of an Excel table to select the column data but not the header.
a. Click b. Double-click
c. Triple-click d. Right-click
24. You could test two or more conditions and determine whether all conditions are true using the _____ function.
a. OR b. IF
c. AND d. THIS
25. If all of the logical conditions are true, the AND function returns the logical value _____.
a. FALSE b. ON
c. TRUE d. OFF
26. You can include up to _____ logical conditions in the AND function.
a. 25 b. 50
c. 225 d. 255
27. The _____ function calculates the difference between two dates and shows the result in months, days, or years.
a. nested IF b. CALIF
c. DATEDIF d. DATE
28. With the DATEDIF function, the _____ interval code means months.
a. m b. mo
c. mon d. mth
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
29. _____ references make it easier to create formulas that use portions or all of an Excel table because the names of
headers are usually simpler to identify than cell addresses.
a. Indexed b. Structured
c. Key d. Logical
30. When you want to reference an entire column of data in a table, you create a column _____.
a. label b. name
c. variable d. qualifier
31. When you create a calculated column, you can use _____ references to create the formula.
a. indexed b. structured
c. key d. logical
32. If you are creating a calculated column or formula within an Excel table, you can use the _____ structured reference in
the formula.
a. basic b. advanced
c. qualified d. restricted
33. You can _____ IF functions to allow for three or more outcomes.
a. loop b. cycle
c. nest d. link
34. Lookup _____ allow you to use tables of data to “look up” values and insert them in another worksheet location.
a. functions b. formulas
c. equations d. calculations
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
35. The _____ function searches vertically down the first column of the lookup table.
a. VLOOK b. VLOOKUP
c. HLOOK d. HLOOKUP
36. When using VLOOKUP, the _____argument is optional.
a. lookup_value b. table_array
c. range_lookup d. col_index
37. Using the VLOOKUP function, an exact match lookup occurs when the value entered must match one of the values in
_____ of the lookup table.
a. the first column b. any column
c. the first row d. any row
38. If you see _____ as the result of a VLOOKUP formula, you might have entered a letter for the col_index_num instead
of a number.
a. #NULL b. #REF?
c. #NAME? d. #DIV/0
39. You can use a _____ function to return a value from a table that is based on an approximate match lookup.
a. VLOOKUP b. VAVERAGE
c. VMATCH d. VAPPROXIMATE
40. An error value begins with a(n) _____ followed by an error name that indicates the type of error.
a. number sign b. asterisk
c. exclamation point d. plus sign
41. The major difference between HLOOKUP and VLOOKUP functions is the way the lookup tables are _____.
a. named b. organized
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
c. saved d. opened
42. The _____ error value means that a value is not available to a function or formula.
a. #NAME? b. #N/A
c. #NULL! d. #NUM!
43. The Conditional Formatting button provides access to the _____.
a. Replicate Values conditional format b. Manage Rules option
c. Criteria option d. Organize Rules option
44. You can edit existing conditional formatting rules from the Conditional Formatting _____ dialog box. Click the Edit
Rule button and make the appropriate changes.
a. Rules Editor b. Rules Manager
c. Editor d. Manager
45. The _____ function calculates the number of cells in a range that match criteria you specify.
a. NUMBERIF b. COUNTIF
c. NUMBERAS d. COUNTAS
46. The _____ function adds the values in a range that match criteria you specify.
a. ADDIF b. ADDAS
c. SUMIF d. SUMAS
47. The _____ function calculates the average of values in a range that match criteria you specify.
a. AVERAGEAS b. AVERAGEOR
c. AVERAGEAND d. AVERAGEIF
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
48. To change the background fill color of duplicate values, in the _____ group on the Home tab, click the Conditional
Formatting button, and then click Manage Rules.
a. Format b. Options
c. Styles d. Tools
49. Each time you create a conditional format, you are defining a conditional formatting _____.
a. theme b. object
c. condition d. rule
50. You can edit existing conditional formatting _____ from the Conditional Formatting Rules Manager dialog box.
a. themes b. objects
c. tables d. rules
51. For conditional formatting, a formula must be in the form of a(n) _____ test that results in a true or false value.
a. conditional b. hypothetical
c. logical d. approximate
52. In the Edit Formatting Rule dialog box, click the _____ button to open the Format Cells dialog box.
a. Format Cells b. Options
c. Format d. Cell Tools
53. The COUNTIF function is sometimes referred to as a(n) _____ count.
a. network b. criterion
c. conditional d. basic
54. You can use _____ to reference cells within an Excel table.
a. unstructured references b. cell and range addresses
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
c. macros d. links
Case-Based Critical Thinking Questions
Case 8-1
Evaluate the formula =AND(G2=“FT”,M2>=1) based upon the information provided.
55. What value is returned assuming that G2 contains the value PT and M2 contains the value 2?
a. TRUE b. FALSE
c. PT d. PT, 2
56. What value is returned assuming that G2 contains the value FT and M2 contains the value 2?
a. TRUE b. FALSE
c. PT d. PT, 2
Case-Based Critical Thinking Questions
Case 8-2
Your boss in the human resources department asks you to write a function that calculates the length of time that
employees have been with the firm, using the DATEDIF function. Assume that C2 contains the hire date for a given
employee and that cell $AE$2 contains the cut-off date against which to compare the hire date to determine length of
service.
57. For one service recognition program, you need to calculate the length of service in complete months. What is the
correct formula?
a. =DATEDIF(C2, $AE$2, “m”) b. =DATEDIF($AE$2, C2, “month”)
c. =DATEDIF($AE$2, C2, “mth”) d. =DATEDIF(C2, $AE$2, “mon”)
58. For a separate service recognition program, you need to calculate the length of service in complete years. What is the
correct formula?
a. =DATEDIF(C2, $AE$2, “yr”) b. =DATEDIF($AE$2, C2, “year”)
c. =DATEDIF($AE$2, C2, “yr”) d. =DATEDIF(C2, $AE$2, “y”)
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
59. On a lark, for a year-end trivia contest, your boss wants you to calculate the length of service in complete days. What
is the correct formula?
a. =DATEDIF(C2, $AE$2, “today”) b. =DATEDIF($AE$2, C2, “dy”)
c. =DATEDIF($AE$2, C2, “day”) d. =DATEDIF(C2, $AE$2, “d”)
Case-Based Critical Thinking Questions
Case 8-3
You have been using a variety of nested IF functions to calculate costs in your spreadsheet when a colleague stops by and
comments on what you have been working on, suggesting that there is an easier approach to use.
60. Which of the following is an effective alternative for the use of extensive nested IF functions?
a. compare table b. function table
c. lookup table d. case table
61. You implement the VLOOKUP formula at your colleague’s suggestion, but you get the #VALUE! error as the result
of the formula. Which of the following is likely true?
a. You might have entered a table name for the table_array value instead of the cell reference of the lookup table.
b. You might have indicated that the compare values are a range of values instead of an exact match.
c. You might have entered a number for the col_index_num instead of a letter.
d. You might have entered a letter for the col_index_num instead of a number.
62. If you omit the range_lookup value from a LOOKUP function, which of the following is true?
a. The function returns the #NAME? error value.
b. Your function can only search the first column of the lookup table.
c. It is considered an approximate match.
d. It is considered an exact match.
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
63. The ____________________ function is a logical function that returns a TRUE value if all the logical conditions are
true and a FALSE value if any or all of the logical conditions are false.
64. When you create a formula that references all or parts of an Excel table, you can replace the specific cell or range
address with a(n) ____________________ reference, the actual table name or column header.
65. The ____________________ function calculates the difference between two dates and shows the result in months,
days, or years.
66. A(n) ____________________ IF function is when one IF function is placed inside another IF function to test an
additional condition.
67. The ____________________ function is a logical function that returns a TRUE value if any of the logical conditions
are true and a FALSE value if the logical conditions are false.
68. The ____________________ function adds the values in a range that meet criteria you specify.
69. The ____________________ function is used to change the orientation of a range—that is, return a vertical range of
cells as a horizontal range, or vice versa.
70. The ____________________ function searches vertically down a lookup table and is used when the compare values
are stored in the first column of the lookup table.
Name:
Class:
Date:
Excel Module 08: Working with Advanced Functions
71. The ____________________ function searches horizontally across a lookup table and is used when the compare
values are stored in the first row of the lookup table.
72. ____________________ formatting changes a cell’s formatting when its contents match a specified condition.
73. A(n) ____________________ specifies the type of condition, the type of formatting when that condition occurs, and
the cell or range the formatting is applied to.
74. A(n) ____________________ match lookup occurs when the lookup value is found within a range of numbers in the
first column (or row) of the lookup table.
75. The ____________________ function can determine if a cell contains an error value and displays the message you
choose rather than the default error value.