Excel: Formulas and Functions Flashcards

What does the SUMPRODUCT function do?

Answer: Multiplies corresponding elements of arrays and returns their sum

SUMPRODUCT multiplies corresponding elements across two or more arrays and then sums all those products.

Which formula correctly uses an absolute reference so that C1 does not change when the formula is copied down column D?

Answer: =A1*$C$1

Using $C$1 locks both the column and row of C1, so it remains constant when the formula is copied in any direction.

What is the correct syntax for COUNTIFS to count rows where column A is 'East' AND column B is greater than 100?

Answer: =COUNTIFS(A:A,"East",B:B,">100")

COUNTIFS takes alternating range/criteria pairs, so the correct syntax is =COUNTIFS(A:A,"East",B:B,">100").

Which function returns the position of the minimum value within a range?

Answer: MATCH(MIN(...),...)

To find the position of the minimum value, wrap MIN inside MATCH: =MATCH(MIN(range), range, 0).

What does =TEXT(1234.5, "$#,##0.00") return?

Answer: $1,234.50

TEXT formats a number as a text string using the specified format code, producing "$1,234.50" with a dollar sign, comma separator, and two decimals.

Which Excel function calculates the number of working days between two dates, excluding weekends?

Answer: NETWORKDAYS

NETWORKDAYS returns the number of whole working days between two dates, automatically excluding weekends (and optional holidays).

A formula returns the error #NAME?. What is the most likely cause?

Answer: A function name is misspelled or unrecognized

#NAME? appears when Excel does not recognize a name in a formula, most commonly due to a misspelled function name or undefined range name.