Deck 7: Using Advanced Functions and Conditional Formatting

Question 1

According to the dialog box in the accompanying figure,the lookup_value is found in row 2 of the table.

Answer 1

True

Question 2

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

Answer 2

True

Question 3

Even if the compare values are not arranged in order,Excel can still retrieve the correct results from a lookup table used with a range of values.

Answer 3

False

Question 4

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

Question 5

According to the dialog box in the accompanying figure,the user is not looking for an exact match.

Question 6

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

Question 7

Nesting IF functions will allow for three or more outcomes.

Question 8

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.

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.

Question 10

When a lookup table is used with a range of values,the compare values must be sorted in alphabetical order if they are text.

Question 11

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.

Question 12

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.

Question 13

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

Question 14

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

Question 15

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

Question 16

Calculated columns work only in ranges of data.

Question 17

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

Question 18

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.

Question 19

Whenever you enter a formula into an empty table row,Excel automatically fills the rest of that table row with the formula.

Question 20

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

Question 21

The IF function results in two values._________________________

Question 22

You can calculate the number of cells in a range that match criteria you specify using the SUMIF function._________________________

Question 23

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Use to reference cells within an Excel table

Question 24

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

Question 25

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

Question 26

An error value begins with a number sign followed by an error name,which indicates the type of error._________________________

Question 27

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Excel table column containing data derived from other columns

Question 28

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Adds the values in a range that meet criteria you specify

Question 29

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

Question 30

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
You can calculate the number of cells in a range that match criteria you specify using this function

Question 31

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

Question 32

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

Question 33

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

Question 34

When manually creating a conditional formatting formula,if the formula references a cell or range outside the table,relative references should be used._________________________

Question 35

In an unqualified structured reference,only the column qualifier appears in the reference._________________________

Question 36

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Use to calculate the average of values in a range that meet criteria you specify

Question 37

You use the MEANIF function to calculate the average of values in a range that meet criteria you specify._________________________

Question 38

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

Question 39

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

Question 40

You cannot nest more than one IF function._________________________

Question 41

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Searches down the first column of the lookup table

Question 42

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Searches across the first row of the lookup table

Question 43

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

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

Question 44

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

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

Question 45

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Use to highlight duplicate values in a range with a font and/or fill color

Question 46

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 47

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 48

____ just above a cell in 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 49

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 50

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Adds the values in a range

Question 51

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 52

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

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

Question 53

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 54

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 55

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Calculates the average of the values in a range

Question 56

After a calculated column contains an inconsistency,any other edits you make to that column are no longer automatically applied to the rest of the cells in that column because Excel does not overwrite ____ values.

A) indexed
B) marginal
C) custom
D) filled

Question 57

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
expression that returns either a TRUE value or a FALSE value

Question 58

____ just above a cell in the header row of an Excel table to select the entire column including the column header.

A) Click
B) Double-click
C) Triple-click
D) Right-click

Question 59

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.SUMIF
g.HLOOKUP
b.VLOOKUP
h.COUNT
c.COUNTIF
i.logical condition
d.conditional formatting
j.SUM
e.calculated column
k.AVERAGEIF
f.AVERAGE
l.cell and range addresses
Tallies the number of data values in a range

Question 60

To evaluate a condition in Excel,you use the ____ function.

A) HAVE
B) THIS
C) IF
D) BUT

Question 61

Given the flowchart in the accompanying figure,the Pay Grade of ____ yields a Bonus value of 7500.

A) 1
B) 2
C) 3
D) none of the above

Question 62

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

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

Question 63

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) unqualified
D) restricted

Question 64

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 65

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

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

Question 66

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

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

Question 67

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

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

Question 68

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

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

Question 69

Given the flowchart in the accompanying figure,the Pay Grade of ____ yields a Bonus value of 2500.

A) 1
B) 2
C) both a.and b.
D) neither a.nor b.

Question 70

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

A) =IF([Pay Grade]=2,2500,IF([Pay Grade]=1,5000,7500))
B) =AND([Pay Grade]=2,2500,AND([Pay Grade]=1,5000,7500))
C) =IF([Pay Grade]=1,2500,IF([Pay Grade]=2,5000,7500))
D) =AND([Pay Grade]=1,2500,AND([Pay Grade]=2,5000,7500))

Question 71

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

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

Question 72

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.

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

Question 73

Given the flowchart in the accompanying figure,the Pay Grade of ____ yields a Bonus value of 5000.

A) 1
B) 2
C) 3
D) 4

Question 74

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 75

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

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

Question 76

Placing cell references to input values rather than constants in formulas makes a worksheet ____.

A) more flexible
B) easier to update
C) both a.and b.
D) neither a.nor b.

Question 77

The table that stores the data you want to retrieve is called a lookup table,and it organizes the data you want to retrieve into different ____,such as by product ID.

A) tabs
B) categories
C) cells
D) properties

Question 78

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

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

Question 79

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 80

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