Question 1

The _____ function calculates the average of values in a range that match criteria you specify.

Accepted Answer

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

Question 2

To highlight duplicate values,in the Styles group on the _____ tab,click the Conditional Formatting button,point to Highlight Cells Rules,and then click Duplicate Values.

Accepted Answer

A) HOME 
B) PAGE LAYOUT 
C) FORMULAS 
D) DATA 
A) HOME 
B) PAGE LAYOUT 
C) FORMULAS 
D) DATA

Question 3

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.

Accepted Answer

The answer of The ____________________ function is used to change...

Question 4

An error value begins with a(n)$\underline { \text { number sign } }$ followed by an error name,which indicates the type of error._________________________

Accepted Answer

A) True 
 B)False

Question 5

You can _____ IF functions to allow for three or more outcomes.

Accepted Answer

A) loop 
B) cycle 
C) nest 
D) link 
A) loop 
B) cycle 
C) nest 
D) link

Question 6

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 7

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 8

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. 
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

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
-Changes a cell's formatting when its contents match a specified condition

Accepted Answer

A) SUMIF 
B) VLOOKUP 
C) COUNTIF 
D) conditional formatting 
E) DATEDIF
F)AVERAGE
G)HLOOKUP
H)TRANSPOSE
I)logical condition
J)SUM 
A) SUMIF 
B) VLOOKUP 
C) COUNTIF 
D) conditional formatting 
E) DATEDIF
F)AVERAGE
G)HLOOKUP
H)TRANSPOSE
I)logical condition
J)SUM

Question 10

You can use a _____ function to return a value from a table that is based on an approximate match lookup.

Accepted Answer

A) VLOOKUP 
B) VAVERAGE 
C) VMATCH 
D) VAPPROXIMATE 
A) VLOOKUP 
B) VAVERAGE 
C) VMATCH 
D) VAPPROXIMATE

Question 11

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.
-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?

Accepted Answer

A) =DATEDIF(C2, $AE$2, "today") 
B) =DATEDIF($AE$2, C2, "dy") 
C) =DATEDIF($AE$2, C2, "day") 
D) =DATEDIF(C2, $AE$2, "d") 
A) =DATEDIF(C2, $AE$2, "today") 
B) =DATEDIF($AE$2, C2, "dy") 
C) =DATEDIF($AE$2, C2, "day") 
D) =DATEDIF(C2, $AE$2, "d")

Question 12

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.

Accepted Answer

The answer of A(n)____________________ match lookup occurs when the lookup...

Question 13

____________________ 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...

Question 14

The Conditional Formatting button provides access to the _____.

Accepted Answer

A) Replicate Values conditional format 
B) Manage Rules option 
C) Criteria option 
D) Organize Rules option 
A) Replicate Values conditional format 
B) Manage Rules option 
C) Criteria option 
D) Organize Rules option

Question 15

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

Accepted Answer

A) SUMIF 
B) VLOOKUP 
C) COUNTIF 
D) conditional formatting 
E) DATEDIF
F)AVERAGE
G)HLOOKUP
H)TRANSPOSE
I)logical condition
J)SUM 
A) SUMIF 
B) VLOOKUP 
C) COUNTIF 
D) conditional formatting 
E) DATEDIF
F)AVERAGE
G)HLOOKUP
H)TRANSPOSE
I)logical condition
J)SUM

Question 16

When you want to reference an entire column of data in a table,you create a column _____.

Accepted Answer

A) label 
B) name 
C) variable 
D) qualifier 
A) label 
B) name 
C) variable 
D) qualifier

Question 17

The major difference between HLOOKUP and VLOOKUP functions is the way the lookup tables are _____.

Accepted Answer

A) named 
B) organized 
C) saved 
D) opened 
A) named 
B) organized 
C) saved 
D) opened

Question 18

The COUNTIF function is sometimes referred to as a(n)_____ count.

Accepted Answer

A) network 
B) criterion 
C) conditional 
D) basic 
A) network 
B) criterion 
C) conditional 
D) basic

Question 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 the logical conditions are false.

Accepted Answer

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

Question 20

-The complete formula that corresponds to the flowchart in the accompanying figure is _____.

Accepted Answer

A) =IF([Perf Rating]=2,2500,IF([Perf Rating]=1,2500,7500)) 
B) =AND([Perf Rating]=2,2500,AND([Perf Rating]=1,2500,7500)) 
C) =IF([Perf Rating]=1,0,IF([Perf Rating]=2,2500,7500)) 
D) =AND([Perf Rating]=1,2500,AND([Perf Rating]=2,0,7500)) 
A) =IF([Perf Rating]=2,2500,IF([Perf Rating]=1,2500,7500)) 
B) =AND([Perf Rating]=2,2500,AND([Perf Rating]=1,2500,7500)) 
C) =IF([Perf Rating]=1,0,IF([Perf Rating]=2,2500,7500)) 
D) =AND([Perf Rating]=1,2500,AND([Perf Rating]=2,0,7500))

The _____ function calculates the average of values in a range that match criteria you specify.

To highlight duplicate values,in the Styles group on the _____ tab,click the Conditional Formatting button,point to Highlight Cells Rules,and then click Duplicate Values.

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.

An error value begins with a(n) $\underline { \text { number sign } }$ followed by an error name,which indicates the type of error._________________________

You can _____ IF functions to allow for three or more outcomes.

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.

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

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Changes a cell's formatting when its contents match a specified condition

You can use a _____ function to return a value from a table that is based on an approximate match lookup.

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.

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

The Conditional Formatting button provides access to the _____.

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

When you want to reference an entire column of data in a table,you create a column _____.

The major difference between HLOOKUP and VLOOKUP functions is the way the lookup tables are _____.

The COUNTIF function is sometimes referred to as a(n)_____ count.

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.

-The complete formula that corresponds to the flowchart in the accompanying figure is _____.

Getting Started With Excel

Formatting Workbook Text and Data

Calculating Data With Formulas and Functions

Analyzing and Charting Financial Data

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

Managing Multiple Worksheets and Workbooks

Developing an Excel Application

Exploring Financial Tools and Functions

Performing What If Analyses

Connecting to External Data

Collaborating on a Shared Workbook

Managing Your Files

Filters

Exam 8: Working With Advanced Functions

The _____ function calculates the average of values in a range that match criteria you specify.

To highlight duplicate values,in the Styles group on the _____ tab,click the Conditional Formatting button,point to Highlight Cells Rules,and then click Duplicate Values.

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.

An error value begins with a(n) number sign ‾\underline { \text { number sign } } number sign ​ followed by an error name,which indicates the type of error._________________________

You can _____ IF functions to allow for three or more outcomes.

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.

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

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Changes a cell's formatting when its contents match a specified condition

You can use a _____ function to return a value from a table that is based on an approximate match lookup.

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.

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

The Conditional Formatting button provides access to the _____.

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

When you want to reference an entire column of data in a table,you create a column _____.

The major difference between HLOOKUP and VLOOKUP functions is the way the lookup tables are _____.

The COUNTIF function is sometimes referred to as a(n)_____ count.

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.

-The complete formula that corresponds to the flowchart in the accompanying figure is _____.

Getting Started With Excel

Formatting Workbook Text and Data

Calculating Data With Formulas and Functions

Analyzing and Charting Financial Data

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

Managing Multiple Worksheets and Workbooks

Developing an Excel Application

Exploring Financial Tools and Functions

Performing What If Analyses

Connecting to External Data

Collaborating on a Shared Workbook

Managing Your Files

Filters

An error value begins with a(n) $\underline { \text { number sign } }$ followed by an error name,which indicates the type of error._________________________