Question 1

-According to the dialog box in the accompanying figure,the HealthPlanRates table contains the data you want to look up.

Accepted Answer

A) True 
 B)False

Question 2

-According to the dialog box in the accompanying figure,the column in the HealthPlanRates lookup table that stores the value to be returned is the first column.

Accepted Answer

A) True 
 B)False

Question 3

____ references make it easier to create formulas that use portions or all of an Excel table because the names or headers are usually simpler to identify than cell addresses.

Accepted Answer

A)  Indexed 
B)  Structured 
C)  Key 
D)  Logical 
A)  Indexed 
B)  Structured 
C)  Key 
D)  Logical

Question 4

If all of the logical conditions are true,the AND function returns the logical value ____.

Accepted Answer

A)  FALSE 
B)  ON 
C)  TRUE 
D)  OFF 
A)  FALSE 
B)  ON 
C)  TRUE 
D)  OFF

Question 5

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 a separate service recognition program,you need to calculate the length of service in complete years.What is the correct formula?

Accepted Answer

A)  =DATEDIF(C2,$AE$2,"yr") 
B)  =DATEDIF($AE$2,C2,"year") 
C)  =DATEDIF($AE$2,C2,"yr") 
D)  =DATEDIF(C2,$AE$2,"y") 
A)  =DATEDIF(C2,$AE$2,"yr") 
B)  =DATEDIF($AE$2,C2,"year") 
C)  =DATEDIF($AE$2,C2,"yr") 
D)  =DATEDIF(C2,$AE$2,"y")

Question 6

The IF function results in two values._________________________

Accepted Answer

A) True 
 B)False

Question 7

The ____ error value means that Excel does not recognize text in the formula or function.

Accepted Answer

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

Question 8

The ____________________ function can determine if a cell contains an error value and displays the message you choose rather than the default error value.

Accepted Answer

The answer of The ____________________ function can determine if a...

Question 9

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
-Tallies the number of data 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 10

Lookup ____ allow you to use tables of data to "look up" values and insert them in another worksheet location.

Accepted Answer

A)  functions 
B)  formulas 
C)  equations 
D)  calculations 
A)  functions 
B)  formulas 
C)  equations 
D)  calculations

Question 11

Sometimes the built-in conditional formatting rules do not apply the formatting you need.In these instances,you may be able to create a conditional formatting rule based on a formula that uses a logical expression to describe the condition you want.Please give an example of when you would use this type of formula,and list two guidelines you must follow when creating this type of formula.

Accepted Answer

Example (Answers may vary): You can crea

Question 12

You could test two or more conditions and determine whether all conditions are true using the ____ function.

Accepted Answer

A)  OR 
B)  IF 
C)  AND 
D)  THIS 
A)  OR 
B)  IF 
C)  AND 
D)  THIS

Question 13

An alternative to using several nested IF functions is to use a ____ table.

Accepted Answer

A)  lookup 
B)  value 
C)  comparison 
D)  rating 
A)  lookup 
B)  value 
C)  comparison 
D)  rating

Question 14

In the DATEDIF function,the first date argument is the latest date._________________________

Accepted Answer

A) True 
 B)False

Question 15

In many situations,the value you store in a cell depends on certain ____.

Accepted Answer

A)  conditions 
B)  indices 
C)  functions 
D)  formulas 
A)  conditions 
B)  indices 
C)  functions 
D)  formulas

Question 16

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.

Accepted Answer

The answer of The ____________________ function is a logical function...

Question 17

The ____ function calculates the difference between two dates and shows the result in months,days,or years.

Accepted Answer

A)  nested IF 
B)  CALIF 
C)  DATEDIF 
D)  DATE 
A)  nested IF 
B)  CALIF 
C)  DATEDIF 
D)  DATE

Question 18

Each time you create a conditional format,you are defining a conditional formatting ____.

Accepted Answer

A)  theme 
B)  object 
C)  condition 
D)  rule 
A)  theme 
B)  object 
C)  condition 
D)  rule

Question 19

With the DATEDIF function,the ____ interval code means months.

Accepted Answer

A)  m 
B)  mo 
C)  mon 
D)  mth 
A)  m 
B)  mo 
C)  mon 
D)  mth

Question 20

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

Accepted Answer

The answer of ____________________ formatting changes a cell's formatting when...

-According to the dialog box in the accompanying figure,the HealthPlanRates table contains the data you want to look up.

-According to the dialog box in the accompanying figure,the column in the HealthPlanRates lookup table that stores the value to be returned is the first column.

____ references make it easier to create formulas that use portions or all of an Excel table because the names or headers are usually simpler to identify than cell addresses.

If all of the logical conditions are true,the AND function returns the logical value ____.

The IF function results in two values._________________________

The ____ error value means that Excel does not recognize text in the formula or function.

The ____________________ function can determine if a cell contains an error value and displays the message you choose rather than the default error value.

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Tallies the number of data values in a range

Lookup ____ allow you to use tables of data to "look up" values and insert them in another worksheet location.

You could test two or more conditions and determine whether all conditions are true using the ____ function.

An alternative to using several nested IF functions is to use a ____ table.

In the DATEDIF function,the first date argument is the latest date._________________________

In many situations,the value you store in a cell depends on certain ____.

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.

The ____ function calculates the difference between two dates and shows the result in months,days,or years.

Each time you create a conditional format,you are defining a conditional formatting ____.

With the DATEDIF function,the ____ interval code means months.

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

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

-According to the dialog box in the accompanying figure,the HealthPlanRates table contains the data you want to look up.

-According to the dialog box in the accompanying figure,the column in the HealthPlanRates lookup table that stores the value to be returned is the first column.

____ references make it easier to create formulas that use portions or all of an Excel table because the names or headers are usually simpler to identify than cell addresses.

If all of the logical conditions are true,the AND function returns the logical value ____.

The IF function results in two values._________________________

The ____ error value means that Excel does not recognize text in the formula or function.

The ____________________ function can determine if a cell contains an error value and displays the message you choose rather than the default error value.

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Tallies the number of data values in a range

Lookup ____ allow you to use tables of data to "look up" values and insert them in another worksheet location.

You could test two or more conditions and determine whether all conditions are true using the ____ function.

An alternative to using several nested IF functions is to use a ____ table.

In the DATEDIF function,the first date argument is the latest date._________________________

In many situations,the value you store in a cell depends on certain ____.

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.

The ____ function calculates the difference between two dates and shows the result in months,days,or years.

Each time you create a conditional format,you are defining a conditional formatting ____.

With the DATEDIF function,the ____ interval code means months.

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

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