Question 1

Error values indicate that some element in a ____ is preventing Excel from returning a calculated value.

Accepted Answer

A)  formula 
B)  cell referenced in a formula 
C)  either a.or b. 
D)  neither a.nor b. 
A)  formula 
B)  cell referenced in a formula 
C)  either a.or b. 
D)  neither a.nor b.

Question 2

In the Styles group on the Home tab,click the Conditional Formatting button,and then click ____ to open the Conditional Formatting Rules Manager dialog box.

Accepted Answer

A)  Conditional Formatting Rules Management 
B)  Conditions 
C)  Manage Rules 
D)  Formatting Rules 
A)  Conditional Formatting Rules Management 
B)  Conditions 
C)  Manage Rules 
D)  Formatting Rules

Question 3

The #REF! error value can occur when you supply a range of values to a function that requires a single value._________________________

Accepted Answer

A) True 
 B)False

Question 4

In a fully qualified structured reference,the table name follows the column qualifier._________________________

Accepted Answer

A) True 
 B)False

Question 5

The Conditional Formatting button provides access to the ____.

Accepted Answer

A)  Duplicate Values conditional format 
B)  Manage Rules option 
C)  both a.and b. 
D)  neither a.nor b. 
A)  Duplicate Values conditional format 
B)  Manage Rules option 
C)  both a.and b. 
D)  neither a.nor b.

Question 6

If any logical condition listed in the function is true,the AND function returns a TRUE value._________________________

Accepted Answer

A) True 
 B)False

Question 7

The compare values,the categories for a lookup table,are located in the table's last column or row.

Accepted Answer

A) True 
 B)False

Question 8

Using the VLOOKUP function,an exact match lookup occurs when the value entered must match one of the values in ____ of the lookup table.

Accepted Answer

A)  the first column 
B)  any column 
C)  the first row 
D)  any row 
A)  the first column 
B)  any column 
C)  the first row 
D)  any row

Question 9

If you need to track information such as the specific age,a best practice is to use calculations to determine this value based on values in the Birth Date field.

Accepted Answer

A) True 
 B)False

Question 10

Using the VLOOKUP function,when the lookup value matches a(n)____ value,the corresponding value from the lookup table is returned to the cell with the lookup formula.

Accepted Answer

A)  key 
B)  index 
C)  compare 
D)  rating 
A)  key 
B)  index 
C)  compare 
D)  rating

Question 11

When the lookup value matches a compare value,the corresponding value from the lookup table is returned to the cell with the lookup formula.

Accepted Answer

A) True 
 B)False

Question 12

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
-Calculates the average of the values in a range

Accepted Answer

A) SUMIF 
B) VLOOKUP 
C) COUNTIF 
D) conditional formatting 
E) calculated column
F)AVERAGE
G)HLOOKUP
H)COUNT
I)logical condition
J)SUM
K)AVERAGEIF
L)cell and range addresses 
A) SUMIF 
B) VLOOKUP 
C) COUNTIF 
D) conditional formatting 
E) calculated column
F)AVERAGE
G)HLOOKUP
H)COUNT
I)logical condition
J)SUM
K)AVERAGEIF
L)cell and range addresses

Question 13

Case-Based Critical Thinking Questions
Case 7-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.
-For one service recognition program,you need to calculate the length of service in complete months.What is the correct formula?

Accepted Answer

A)  =DATEDIF(C2,$AE$2,"m") 
B)  =DATEDIF($AE$2,C2,"month") 
C)  =DATEDIF($AE$2,C2,"mth") 
D)  =DATEDIF(C2,$AE$2,"mon") 
A)  =DATEDIF(C2,$AE$2,"m") 
B)  =DATEDIF($AE$2,C2,"month") 
C)  =DATEDIF($AE$2,C2,"mth") 
D)  =DATEDIF(C2,$AE$2,"mon")

Question 14

The #VALUE! error value can occur when a cell used by the function was deleted from the worksheet._________________________

Accepted Answer

A) True 
 B)False

Question 15

Duplicate value highlighting helps to verify that columns of data have unique entries._________________________

Accepted Answer

A) True 
 B)False

Question 16

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.

Accepted Answer

A) True 
 B)False

Question 17

The ____ function calculates the number of cells in a range that match criteria you specify,such as counting the number of company employees located in Austin.

Accepted Answer

A)  NUMBERIF 
B)  COUNTIF 
C)  NUMBERAS 
D)  COUNTAS 
A)  NUMBERIF 
B)  COUNTIF 
C)  NUMBERAS 
D)  COUNTAS

Question 18

The ____ function adds the values in a range that match criteria you specify,such as adding the total salary paid to Austin employees.

Accepted Answer

A)  ADDIF 
B)  ADDAS 
C)  SUMIF 
D)  SUMAS 
A)  ADDIF 
B)  ADDAS 
C)  SUMIF 
D)  SUMAS

Question 19

____________________ values indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value.

Accepted Answer

The answer of ____________________ values indicate that some element in...

Question 20

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.

Accepted Answer

The answer of The ____________________ function searches horizontally across a...

Error values indicate that some element in a ____ is preventing Excel from returning a calculated value.

In the Styles group on the Home tab,click the Conditional Formatting button,and then click ____ to open the Conditional Formatting Rules Manager dialog box.

The #REF! error value can occur when you supply a range of values to a function that requires a single value._________________________

In a fully qualified structured reference,the table name follows the column qualifier._________________________

The Conditional Formatting button provides access to the ____.

If any logical condition listed in the function is true,the AND function returns a TRUE value._________________________

The compare values,the categories for a lookup table,are located in the table's last column or row.

Using the VLOOKUP function,an exact match lookup occurs when the value entered must match one of the values in ____ of the lookup table.

If you need to track information such as the specific age,a best practice is to use calculations to determine this value based on values in the Birth Date field.

Using the VLOOKUP function,when the lookup value matches a(n)____ value,the corresponding value from the lookup table is returned to the cell with the lookup formula.

When the lookup value matches a compare value,the corresponding value from the lookup table is returned to the cell with the lookup formula.

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Calculates the average of the values in a range

The #VALUE! error value can occur when a cell used by the function was deleted from the worksheet._________________________

Duplicate value highlighting helps to verify that columns of data have unique entries._________________________

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.

The ____ function calculates the number of cells in a range that match criteria you specify,such as counting the number of company employees located in Austin.

The ____ function adds the values in a range that match criteria you specify,such as adding the total salary paid to Austin employees.

____________________ values indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value.

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.

Getting Started With Excel

Formatting a Workbook

Working With Formulas and Functions

Enhancing a Workbook With Charts and Graphs

Working With Excel Tables, Pivot-tables, and Pivot-charts

Managing Multiple Worksheets and Workbooks

Developing an Excel Application

Working With Financial Tools and Functions

Performing What-If Analyses

Connecting to External Data

Expanding Excel With Visual Basic for Applications

Managing Your Files

Getting Started With Microsoft Office 2010

Filters

Exam 7: Using Advanced Functions and Conditional Formatting

Error values indicate that some element in a ____ is preventing Excel from returning a calculated value.

In the Styles group on the Home tab,click the Conditional Formatting button,and then click ____ to open the Conditional Formatting Rules Manager dialog box.

The #REF! error value can occur when you supply a range of values to a function that requires a single value._________________________

In a fully qualified structured reference,the table name follows the column qualifier._________________________

The Conditional Formatting button provides access to the ____.

If any logical condition listed in the function is true,the AND function returns a TRUE value._________________________

The compare values,the categories for a lookup table,are located in the table's last column or row.

Using the VLOOKUP function,an exact match lookup occurs when the value entered must match one of the values in ____ of the lookup table.

If you need to track information such as the specific age,a best practice is to use calculations to determine this value based on values in the Birth Date field.

Using the VLOOKUP function,when the lookup value matches a(n)____ value,the corresponding value from the lookup table is returned to the cell with the lookup formula.

When the lookup value matches a compare value,the corresponding value from the lookup table is returned to the cell with the lookup formula.

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Calculates the average of the values in a range

The #VALUE! error value can occur when a cell used by the function was deleted from the worksheet._________________________

Duplicate value highlighting helps to verify that columns of data have unique entries._________________________

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.

The ____ function calculates the number of cells in a range that match criteria you specify,such as counting the number of company employees located in Austin.

The ____ function adds the values in a range that match criteria you specify,such as adding the total salary paid to Austin employees.

____________________ values indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value.

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.

Getting Started With Excel

Formatting a Workbook

Working With Formulas and Functions

Enhancing a Workbook With Charts and Graphs

Working With Excel Tables, Pivot-tables, and Pivot-charts

Managing Multiple Worksheets and Workbooks

Developing an Excel Application

Working With Financial Tools and Functions

Performing What-If Analyses

Connecting to External Data

Expanding Excel With Visual Basic for Applications

Managing Your Files

Getting Started With Microsoft Office 2010

Filters