Exam 8: Excell: Working With Advanced Functions

Case-Based Critical Thinking Questions Case 8-1 Evaluate the formula =AND(G2="FT",M2>=1) based upon the information provided. What value is returned assuming that G2 contains the value FT and M2 contains the value 2? C) PT D) PT, 2

When using VLOOKUP, the _____argument is optional.

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

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

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

Nesting IF functions will allow for three or more outcomes.

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

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

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

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

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

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

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.

You can edit existing conditional formatting rules from the Conditional Formatting _____ dialog box. Click the Edit Rule button and make the appropriate changes.

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

The ____________________ function can determine if a cell contains an error value and displays the message you choose rather than the default error value.

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

Case-Based Critical Thinking Questions Case 8-4 The following formula is used to determine Dean's List status: =AND(B1>=12,B2>3.25). Use the information provided to evaluate the formula. Assuming that B1 contains 15 and B2 contains 3.5, then _______.

Case-Based Critical Thinking Questions Case 8-2 Your boss in the human resources department asks you to write a function that calculates the length of time that employees have been with the firm, using the DATEDIF function. Assume that C2 contains the hire date for a given employee and that cell $AE$2 contains the cut-off date against which to compare the hire date to determine length of service. For a separate service recognition program, you need to calculate the length of service in complete years. What is the correct formula?

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