Deck 7: Excel: Using Advanced Functions Conditional Formatting and Filtering

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

According to the dialog box in the accompanying figure the column in the HealthPlanRates lookup table that stores the monthly cost is the first column.

Answer 2

False

Question 3

With the AVERAGEIFS function the associated criteria can be evaluated up to 127 ranges.

Answer 3

True

Question 4

Nesting IF functions will allow for three or more outcomes.

Question 5

Although a criteria range often includes all fields from a table even those that are not needed to select records you have to include all field names from the table when setting up a criteria range.

Question 6

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 7

The conditions H2="Y" and H2="y" return different values.

Question 8

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

Question 9

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

Question 10

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

Question 11

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

Question 12

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 13

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

Question 14

The SUMIFS and AVERAGEIFS functions have identical syntax.

Question 15

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

Question 16

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 17

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

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

Calculated columns work only in ranges of data.

Question 20

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

Question 21

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

Question 22

If a formula references a cell or range outside the table relative references should be used.

Question 23

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

Question 24

Testing for text values is case-sensitive.

Question 25

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 26

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 27

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 28

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

Question 29

The IF function results in two values .

Question 30

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

Question 31

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

Question 32

You cannot nest more than one IF function.

Question 33

In most cases a formula should use absolute references and point to the first row of data in a table.

Question 34

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

Question 35

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

Question 36

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

Question 37

In the DATEDIF function the first date is the latest date.

Question 38

You can use the ____ to combine multiple fields into one cell.

A)ampersand
B)caret
C)pound sign
D)percent symbol

Question 39

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 40

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(n)unqualified reference.

Question 41

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 42

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

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

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

Question 44

The ____ qualifier references the header row in the table.

A)#Data
B)#All
C)#Headers
D)#Totals

Question 45

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 46

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 47

____ the top portion of the column header in an Excel table to select the column data but not the header.

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

Question 48

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

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

Question 49

You can insert a selected function from the list of valid function names by pressing the ____ key.

A)Shift
B)Alt
C)Ctrl
D)Tab

Question 50

The ____ qualifier references the data in the table.

A)#Data
B)#All
C)#Headers
D)#Totals

Question 51

The ____ qualifier references the entire table.

A)#Data
B)#All
C)#Headers
D)#Totals

Question 52

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

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

Question 53

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

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

Question 54

____ the top portion of a column header in an Excel table to select the entire column including the column header.

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

Question 55

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

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

Question 56

The ____ qualifier references the current row in the specified column of the table.

A)#Data
B)#All
C)#Headers
D)#ThisRow

Question 57

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

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

Question 58

The ____ qualifier references the Total row in the table.

A)#Data
B)#All
C)#Headers
D)#Totals

Question 59

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 60

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

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 62

Given the flowchart in the accompanying figure a Health Plan value of ____ yields a Health Plan Cost of 875.

A)HMOF
B)HMOI
C)PPOF
D)PPOI

Question 63

You can type a(n)____ to the left of the = sign in a formula to convert it to text.

A)ampersand
B)apostrophe
C)caret
D)comma

Question 64

You can enter a column header in a formula by starting to type the name of the column header until it is highlighted in the list and then pressing the ____ key.

A)Esc
B)Tab
C)Alt
D)Ctrl

Question 65

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 66

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 67

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 68

Given the flowchart in the accompanying figure a Health Plan value of ____ yields a Health Plan Cost of 0.

A)HMOF
B)HMOI
C)PPOF
D)None

Question 69

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

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

Question 70

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

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

Question 71

With the DATEIF function the ____ interval code means months.

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

Question 72

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

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

Question 73

When a lookup value matches a particular ____ value a value from an appropriate column in the lookup table is returned to the cell in which the lookup formula is entered or used as part of a more complex formula.

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

Question 74

Given the flowchart in the accompanying figure a Health Plan value of ____ yields a Health Plan Cost of 1650.

A)HMOF
B)HMOI
C)PPOF
D)PPOI

Question 75

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

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

Question 76

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 77

Given the flowchart in the accompanying figure the Pay Grade of ____ yields a Bonus value of Invalid pay grade.

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

Question 78

Given the flowchart in the accompanying figure a Health Plan value of ____ yields a Health Plan Cost of 950.

A)HMOF
B)HMOI
C)PPOI
D)None

Question 79

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

A)=IF(Pay Grade=1 2500 IF(Pay Grade=2 5000 IF(Pay Grade=3 7500 "Invalid pay grade")))
B)=IF[Pay Grade]=1 2500 IF[Pay Grade]=2 5000 IF[Pay Grade]=3 7500 "Invalid pay grade"
C)=IF([Pay Grade]=1 2500 IF([Pay Grade]=2 5000 IF([Pay Grade]=3 7500 "Invalid pay grade")))
D)=IF

Question 80

Given the flowchart in the accompanying figure a Health Plan value of ____ yields a Health Plan Cost of 1500.

A)HMOF
B)HMOI
C)PPOF
D)PPOI