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

Functions that perform summary data analysis on a table of values based on criteria that you set are called the ____ function.

The IF function results in two values .

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

A formula must start with a(n)____.

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

The ____ qualifier references the entire table.

For conditional formatting a formula must be in the form of a ____ text that results in a true or false value.

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.

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.

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

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

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.

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

The _________________________ error value means that the formula or function contains a number divided by 0.

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

Nesting IF functions will allow for three or more outcomes.

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

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

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.