Excel

Use the IFS function to return 5 if cell A5 contains the value 3, and return a 6 if it contains any other number.

IFS(A5=3,5,1, 6)

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 function will return TRUE if cell G7 is blank?

ISNONTEXT(G7)

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

LARGE(B:B,2)

Write a formula to return the first three characters and the last three characters in cell J1.

LEFT(J1,3)&RIGHT(J1,3) ?

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

Vermont!A3+'New Hampshire'!A3

Which Excel function capitalizes only the first letter in someone's name?

PROPER

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

Saturday

Which Excel category contains the SWITCH function?

logical

The ISFORMULA function can be used with _____ to highlight all cells in a worksheet that contain a formula.

conditional formatting

Dragging the name of a worksheet with the _____ key will duplicate the sheet.

ctrl

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

false

Worksheet names that contain a space must begin and end with _____.

single quote

What will be inserted when you press Ctrl+;?

the current date

When using SUMIFS, why is it a good idea to use entire column references?

the function will not need to be changed as the criteria list grows or shrinks

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

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

true

Cell D4 contains a ten-digit phone number with hyphens after the third and sixth digits. Which function will remove the hyphen after the sixth digit?

SUBSTITUTE(D4,"-","",2)

All range names are _____ addresses.

absolute

Which function returns TRUE if cell A1 contains a #N/A error?

ISERROR(A1)

To aggregate using the average, the first argument in the AGGREGATE formula should be _____.

1 (THE FUNCTION NUMBER FOR AVERAGE)

Write a function to return the color of cell R9.

CELL("COLOR", r9)

Write a CHOOSE function that will determine whether a date in cell A1 occurs on a weekday or weekend.

CHOOSE(WEEKDAY(A1), "weekend", "weekday", "weekday", "weekday", "weekday", "weekday", "weekend") ?

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 formula will return the number of blank cells in cells A1:A100?

COUNTBLANK(A1:A100)

To select all blank cells in a list, use the Special menu. Which shortcut opens the Go To menu that contains the Special button?

F5

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

FORMULATEXT(A2)

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)

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

MID(G5, 5, 4)

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

-16 *int() rounds number down to nearest integer

Cell E1 contains the date 8/22, and cell E2 contains the date 10/21. What is the result of DATEDIF(E1,E2"m")?

1

A _____ list is preferred by most people because they think it is easier to read.

vertical

The functions TODAY and NOW are dynamic and are based on _____.

your system's clock

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

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

What is the keyboard shortcut for the Show Formulas button?

CTRL + TILDE ~`

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 function returns the number of characters in cell T4?

LEN(T4)

An employee list shows salaries in column B and the number of years worked in column C. Write a formula to find the lowest salary for employees who have worked longer than five years.

MINIFS(B:B,C:C">=5")

Which MOD formula will return 2?

MOD(12,5)

Create a formula that will return the data in the last cell in column A that is not blank.

OFFSET(A1,COUNTA(A:A)-1,0)

Your boss asks you to generate a random number between 0 and 100. Which formula will provide this result?

RANDBETEWEN(00,100)

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 returns an error if the argument passed to it is greater than 3999? (this card begins exam portions)

ROMAN

Cell C1 contains an employee salary. Write an Excel formula to round the salary to the nearest $100.

ROUND(C1,-2)

An employee worksheet lists employee names in column A and their salaries in column B. Which formula shows the total payroll, and will not need to be changed if the list gets larger or smaller?

SUM(B:B)

An IF statement nested within another IF statement will produce how many possible results?

three

What is the TRIM function used for?

to remove leading and trailing spaces * leaves just one space btwn all words inside string

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

2

Which two-digit year will Excel identify as in the twenty-first century and not the twentieth?

25

What will be displayed with the formula DATE(2020,13,15)?

44211 *internally saves as the difference in days between Jan 1, 1900 and the date given

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

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.

The EDATE function requires two arguments. Which type is the second argument?

months *start_date is the first

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

not having a left VLOOKUP