Exam 8: Excel 4: Working With Advanced Functions

The ____________________ function searches vertically down a lookup table and is used when the compare values are stored in the first column of the lookup table.

When you create a formula that references all or parts of an Excel table, you can replace the specific cell or range address with a(n)____________________ reference, the actual table name or column header.

The ____________________ function adds the values in a range that meet criteria you specify.

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.

The Conditional Formatting button provides access to the _____.

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

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

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

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.

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

You cannot nest more than one IF function to test for multiple outcomes. _________________________

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.

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

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

The ____________________ function is used to change the orientation of a range-that is, return a vertical range of cells as a horizontal range, or vice versa.

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

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?

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 the remaining cells in that table column.

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.

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 one service recognition program, you need to calculate the length of service in complete months. What is the correct formula?