Quiz 5

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

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

Using the VLOOKUP function, an exact match lookup occurs when the value entered must match one of the values in _____ of the lookup table.

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.

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

If you are creating a calculated column or formula within an Excel table, you can use the _____ structured reference in the formula.

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

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

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

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

Nesting IF functions will allow for three or more outcomes.

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

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.

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

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

An error value begins with a(n) _____ followed by an error name that indicates the type of error.

When using VLOOKUP, the _____argument is optional.

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

The IF function evaluates a(n) single condition

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

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

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

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

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

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.

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

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

If you are not sure of a tables name, click in the table, click the ___ tab on the ribbon, and then click the table name box in the Properties group. Table tools design.

For conditional formatting, a formula must be in the form of a(n) ____ test that results in a true or false value

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

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

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

To get the effect of calculated column in a range of data, you must copy and paste the formula or use the _____ feature.

______ just above the header row of an Excel table to select the column data but not the header.

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

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

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

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

The Conditional Formatting button provides access to the _____.

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

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.

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

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

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:

______ 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:

If all of the logical conditions are true, the AND function returns the logical value ______:

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

An error value begins with a(n) number sign followed by an error name that indicates the type of error.

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