Advanced Formulas + Functions

The combination of the MATCH and INDEX functions is referred to as a workaround for Excel's limitation of _____.

not having a left VLOOKUP

What is the third argument in an IF statement?

the return value if FALSE

When using VLOOKUP to find an approximate match, which argument is the index number of the lookup column?

third

The WEEKDAY function returns a number from 1 to 7. What does the number 7 represent?

Saturday

If cell D3 contains the value -15.1, what is the result of INT(D3)?

-16 With negative numbers, the INT function founds to the lower number

When using the UNIQUE function on an entire column, which value will be included in the results?

0

What is the result of this statement: IF(5>4 AND 4>5, 1, 2)?

2

Cells A1:A4 contain the values 2, 5, 7, and 8. What does MEDIAN(A1:A4) return?

6

What is the result of the formula CEILING(8.79,.05)?

8.8

Which SUBTOTAL argument will calculate SUM?

9

When searching cells A2:A300, which MATCH function will return the row number where a match to cell B1 is found?

MATCH(B1,A:A,0)

What is the correct function to show the formula in cell A2?

FORMULATEXT(A2)

Cell G5 contains the text string FOOTBALL. Which function will return the text BALL?

MID(G5, 5, 4)

Which value for the fourth argument will produce an exact match when using VLOOKUP?

False

When tracing the dependence on a cell in a worksheet, how can you tell whether a dependency for that cell exists in another worksheet?

A dotted black line points to an icon for a Sheet.

Write a function to return the color of cell R9.

CELL("color", R9)

Use CONCATENATE to combine the text strings in cells A1, B2, and C3.

CONCATENATE(A1, B2, C3)

Which Excel function counts the number of cells that have numerical data in them?

COUNT

Which COUNTIF formula will count any cell in column B that contains the letters XY?

COUNTIF(B:B, "*XY*")

Which sequence will add 5 to each number in cells A1, A2, and A3?

Enter 5 in cell A4. Select A4, select Ctrl+C, highlight cells A1-A3, select Ctrl+Alt+V, select Add, and then select OK.

Which INDIRECT formula will return the contents of cell D8 from a worksheet named Quarterly Sales?

INDIRECT(" ' Quarterly Sales ' ! D8") *spaces in worksheet names must be enclosed in single quotes and then double quotes

Which formula returns the second-highest number in column B?

LARGE(B:B, 2)

Which function returns the number of characters in cell T4?

LEN(T4)

Which Excel category contains the SWITCH function?

Logical

You need to update cell A1 so that every time the workbook is opened, the date will update itself and show the current date and time. Which function should you use?

NOW( )

Cells B1:B25 contain salaries. Write a formula to return the rank of cell B8, with the values ranked in ascending order.

RANK(B8, B1:B25, 1)

Which formula will produce the sum of cells A3 in two worksheets named Vermont and New Hampshire?

Vermont!A3+'New Hampshire'!A3

A project starts on Jan 15, and this date is in cell A1. Which Excel formula will calculate the end date if the project lasts 100 days, and weekends are not counted as working days?

WORKDAY(A1, 100)