Exam 7: Using Advanced Functions and Conditional Formatting

MATCHING Identify the letter of the choice that best matches the phrase or definition. -You can calculate the number of cells in a range that match criteria you specify using this function

Case-Based Critical Thinking Questions Case 7-3 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?

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

A(n)____________________ specifies the type of condition,the type of formatting when that condition occurs,and the cell or range the formatting is applied to.

What are the guidelines to keep in mind when creating fields in an Excel table?

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.

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

Case-Based Critical Thinking Questions Case 7-1 Evaluate the following formulas and functions. -The following formula is used to determine Dean's List status.Assuming that B1 contains 12 and B2 contains 3.25,then =AND(B1>=12,B2>3.25),then ____.

The ____________________ function enables you to display a more descriptive message that helps users fix a problem rather than adding confusion,as error values often do.

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

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

To retrieve a particular value from a lookup table,a(n)____________________ needs to match the compare values.

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

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

____ lookup tables are commonly used to find a taxpayer's tax rate in a tax table,find a shipping charge based on the weight of a package in a shipping charges table,or determine a student's letter grade from a table of grading criteria.

To filter records by color,click the filter arrow to open the Filter menu,and then click ____________________.

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Adds the values in a range

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.

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

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