Exam 8: Working With Advanced Functions

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.

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Used to change the orientation of a range

Discuss strategies for checking formulas for matching parentheses.

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?

If you see _____ as the result of a VLOOKUP formula,you might have entered a letter for the col_index_num instead of a number.

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

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

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

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?

_____ references make it easier to create formulas that use portions or all of an Excel table because the names of headers are usually simpler to identify than cell addresses.

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?

The _____ function adds the values in a range that match criteria you specify.

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

MATCHING Identify the letter of the choice that best matches the phrase or definition. -Calculates the difference between two dates

If any logical condition listed in the function is true,the $\underline{\text { AND }}$ function returns a TRUE value._________________________

The _____ function searches vertically down the first column of the lookup table.

If you edit a cell in a calculated column so it is no longer consistent with the other formulas in the column,a _____ appears in the upper-left corner of the cell.

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?

Using the VLOOKUP function,an exact match lookup occurs when the value entered must match one of the values in _____ of the lookup table.

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