Question 1

All the logical conditions listed in an AND function must be true for the AND function to return a TRUE value.

Accepted Answer

A) True 
 B)False

Question 2

The ____ function calculates the average of values in a range that match criteria you specify,such as calculating the average salary paid to employees in each city.

Accepted Answer

A)  AVERAGEAS 
B)  AVERAGEOR 
C)  AVERAGEAND 
D)  AVERAGEIF

Question 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 that table column.

Accepted Answer

A) True 
 B)False

Question 4

If you use a structured reference outside a table or in another worksheet to reference an Excel table or portion of the table,you need to use a fully unqualified reference._________________________

Accepted Answer

A) True 
 B)False

Question 5

The ____________________ error value means that the formula or function contains a number divided by 0.

Accepted Answer

The answer of The ____________________ error value means that the...

Question 6

You can include up to ____ logical conditions in the AND function.

Accepted Answer

A)  25 
B)  50 
C)  225 
D)  255

Question 7

You cannot nest more than one IF function._________________________

Accepted Answer

A) True 
 B)False

Question 8

Case-Based Critical Thinking Questions
Case 7-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.
-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?

Accepted Answer

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.

Question 9

Column ____ provide a description of the data entered in each column.

Accepted Answer

A)  keys 
B)  headers 
C)  indices 
D)  seals

Question 10

In most cases,when manually creating a conditional formatting formula,the formula should use absolute references and point to the first row of data in a table._________________________

Accepted Answer

A) True 
 B)False

Question 11

The ____ error value means that a value is not available to a function or formula.

Accepted Answer

A)  #NAME? 
B)  #N/A 
C)  #NULL! 
D)  #NUM!

Question 12

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.

Accepted Answer

The answer of The ____________________ function searches vertically down a...

Question 13

-Given the dialog box in the accompanying figure,if a value in column L does not match a value in the first column of the HealthPlanRates table,#NULL appears in the cell.

Accepted Answer

A) True 
 B)False

Question 14

When you retrieve a particular value from a lookup table,the ____ value is the value you are trying to find.

Accepted Answer

A)  lookup 
B)  compare 
C)  rating 
D)  absolute

Question 15

Case-Based Critical Thinking Questions
Case 7-1
Evaluate the following formulas and functions.
-Assuming that G2 contains the value PT and M2 contains the value 2,evaluate the formula and tell what value is returned: =AND(G2="FT",M2>=1).

Accepted Answer

A)  TRUE 
B)  FALSE 
C)  PT 
D)  PT,2

Question 16

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.

Accepted Answer

The answer of When you create a formula that references...

Question 17

The ____ error value occurs when an invalid value is specified in the LOOKUP function.

Accepted Answer

A)  #NAME? 
B)  #N/A 
C)  #NULL! 
D)  #NUM!

Question 18

____ formatting changes a cell's formatting when its contents match a specified condition.

Accepted Answer

A)  Theme 
B)  Object 
C)  Conditional 
D)  Optional

Question 19

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
-Use to calculate the average of values in a range that meet criteria you specify

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

Question 20

For the col_index_num value,be sure to enter the number that corresponds to the column's position rather than its column letter;otherwise,the VLOOKUP formula may return ____.

Accepted Answer

A)  #NAME? 
B)  #VALUE! 
C)  either a.or b. 
D)  neither a.nor b.

Exam 7: Using Advanced Functions and Conditional Formatting

All the logical conditions listed in an AND function must be true for the AND function to return a TRUE value.

The ____ function calculates the average of values in a range that match criteria you specify,such as calculating the average salary paid to employees in each city.

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 that table column.

If you use a structured reference outside a table or in another worksheet to reference an Excel table or portion of the table,you need to use a fully unqualified reference._________________________

The ____________________ error value means that the formula or function contains a number divided by 0.

You can include up to ____ logical conditions in the AND function.

You cannot nest more than one IF function._________________________

Column ____ provide a description of the data entered in each column.

In most cases,when manually creating a conditional formatting formula,the formula should use absolute references and point to the first row of data in a table._________________________

The ____ error value means that a value is not available to a function or formula.

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.

-Given the dialog box in the accompanying figure,if a value in column L does not match a value in the first column of the HealthPlanRates table,#NULL appears in the cell.

When you retrieve a particular value from a lookup table,the ____ value is the value you are trying to find.

Case-Based Critical Thinking Questions Case 7-1 Evaluate the following formulas and functions. -Assuming that G2 contains the value PT and M2 contains the value 2,evaluate the formula and tell what value is returned: =AND(G2="FT",M2>=1) .

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.

The ____ error value occurs when an invalid value is specified in the LOOKUP function.

____ formatting changes a cell's formatting when its contents match a specified condition.

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Use to calculate the average of values in a range that meet criteria you specify

For the col_index_num value,be sure to enter the number that corresponds to the column's position rather than its column letter;otherwise,the VLOOKUP formula may return ____.