Deck 8: Working With Advanced Functions

Question 1

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.

Answer 1

True

Question 2

The ORERROR function can determine if a cell contains an error value and then display the message you choose.

Answer 2

False

Question 3

According to the dialog box in the accompanying figure,the MedicalRates table is the one from which the data in your funtion argument will be obtained.

Answer 3

True

Question 4

The IF function evaluates a(n) $\underline { \text { single } }$ condition._________________________

Question 5

The COUNTAS function counts the number of cells within a range that meet multiple criteria.

Question 6

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

Question 7

Two common categories of formula errors in Excel are syntax errors and logic errors.

Question 8

$\underline {\text { Duplicate } }$ value highlighting helps to verify that columns of data have unique entries._________________________

Question 9

The $\underline {\text { 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._________________________

Question 10

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

Question 11

In a fully qualified structured reference,the table name $\underline {\text { follows } }$ the column qualifier._________________________

Question 12

You $\underline {\text { cannot }}$ nest more than one IF function to test for multiple outcomes._________________________

Question 13

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

Question 14

In the DATEDIF function,the first date argument is the $\underline {\text { latest } }$ date._________________________

Question 15

Nesting IF functions will allow for three or more outcomes.

Question 16

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

Question 17

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.

Question 18

If any logical condition listed in the function is true,the $\underline{\text { AND }}$ function returns a TRUE value._________________________

Question 19

A formula that includes a structured reference can be fully qualified or unqualified.

Question 20

You can calculate the number of cells in a range that match criteria you specify using the $\underline {\text { SUMIF }}$ function._________________________

Question 21

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Expression that returns either a TRUE value or a FALSE value

Question 22

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

Question 23

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Used to change the orientation of a range

Question 24

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Searches down the first column of the lookup table

Question 25

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Adds the values in a range that meet criteria you specify

Question 26

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

Question 27

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

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

Question 28

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Changes a cell's formatting when its contents match a specified condition

Question 29

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Adds the values in a range

Question 30

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

Question 31

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Calculates the average of the values in a range

Question 32

_____ 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

Question 33

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Calculates the difference between two dates

Question 34

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

Question 35

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

Question 36

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

Question 37

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Calculates the number of cells in a range that match criteria you specify using this function

Question 38

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
f.AVERAGE
b.VLOOKUP
g.HLOOKUP
c.COUNTIF
h.TRANSPOSE
d.conditional formatting
i.logical condition
e.DATEDIF
j.SUM
Searches across the first row of the lookup table

Question 39

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

Question 40

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

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

Question 41

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

Question 42

The Conditional Formatting button provides access to the _____.

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

Question 43

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

Question 44

_____ 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

Question 45

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

Question 46

The _____ function searches vertically down the first column of the lookup table.

A)VLOOK
B)VLOOKUP
C)HLOOK
D)HLOOKUP

Question 47

Given the flowchart in the accompanying figure,the Perf Rating of _____ yields a Bonus value of 0.

A)1
B)2
C)2500
D)7500

Question 48

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

Question 49

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

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

Question 50

When using VLOOKUP,the _____argument is optional.

A)lookup_value
B)table_array
C)range_lookup
D)col_index

Question 51

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

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

Question 52

When you create a calculated column,you can use _____ references to create the formula.

A)indexed
B)structured
C)key
D)logical

Question 53

If you are not sure of a table's name,click in the table,click the _____ tab on the Ribbon,and then click the Table Name box in the Properties group.

A)LAYOUT
B)FORMAT
C)TABLE TOOLS DESIGN
D)STRUCTURED REFERENCES

Question 54

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

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

Question 55

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

Question 56

Given the flowchart in the accompanying figure,the Perf Rating of _____ yields a Bonus value of 2500.

A)1
B)2
C)0
D)7500

Question 57

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

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

Question 58

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

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

Question 59

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

Question 60

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

Question 61

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.

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

Question 62

You can use _____ to reference cells within an Excel table.

A)unstructured references
B)cell and range addresses
C)macros
D)links

Question 63

Evaluate the formula =AND(G2="FT",M2>=1) based upon the information provided.
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

Question 64

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

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

Question 65

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?

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 66

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

Question 67

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

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

Question 68

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

Question 69

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

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

Question 70

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?

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 71

The _____ function adds the values in a range that match criteria you specify.

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

Question 72

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

Question 73

Evaluate the formula =AND(G2="FT",M2>=1) based upon the information provided.
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

Question 74

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

Question 75

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?

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 76

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?

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 77

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

Question 78

You can edit existing conditional formatting _____ from the Conditional Formatting Rules Manager dialog box.

A)themes
B)objects
C)tables
D)rules

Question 79

The _____ function calculates the number of cells in a range that match criteria you specify.

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

Question 80

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