Exam 8: Excell: Working With Advanced Functions

Sometimes the built-in conditional formatting rules do not apply the formatting you need. In these instances, you may be able to create a conditional formatting rule based on a formula that uses a logical expression to describe the condition you want. Give an example of when you would use this type of formula, and list two guidelines you must follow when creating this type of formula.

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

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. Which of the following is an effective alternative for the use of extensive nested IF functions?

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

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

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?

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

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 PT and M2 contains the value 2? C) PT D) PT, 2

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

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 all the logical conditions are true and a FALSE value if any or all of the logical conditions are false.

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

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

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

If all of the logical conditions are true, the AND function returns the logical value _____.

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

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.

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