MOS Excel: Formulas and Functions 5 — Questions and Answers
Question 1: What does the SUMPRODUCT function do?
- Sums a range and multiplies by a constant
- Multiplies corresponding elements of arrays and returns their sum (Correct answer)
- Finds the product of all values in a range
- Returns the largest product of two ranges
Correct 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.
Question 2: Which formula correctly uses an absolute reference so that C1 does not change when the formula is copied down column D?
- =A1*C1
- =A1*$C$1 (Correct answer)
- =A1*C$1
- =$A1*C1
Correct 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.
Question 3: What is the correct syntax for COUNTIFS to count rows where column A is 'East' AND column B is greater than 100?
- =COUNTIFS(A:A,"East",B:B,">100") (Correct answer)
- =COUNTIF(A:A,"East")+COUNTIF(B:B,">100")
- =COUNTIFS(A:A="East",B:B>100)
- =COUNT(A:A,"East",B:B,">100")
Correct 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").
Question 4: Which function returns the position of the minimum value within a range?
- MIN
- MINIFS
- MATCH(MIN(...),...) (Correct answer)
- SMALL
Correct answer: MATCH(MIN(...),...)
To find the position of the minimum value, wrap MIN inside MATCH: =MATCH(MIN(range), range, 0).
Question 5: What does =TEXT(1234.5, "$#,##0.00") return?
- 1234.5
- $1,234.50 (Correct answer)
- 1,234.50
- #,##0.00
Correct 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.
Question 6: Which Excel function calculates the number of working days between two dates, excluding weekends?
- DAYS
- NETWORKDAYS (Correct answer)
- WORKDAY
- DATEDIF
Correct answer: NETWORKDAYS
NETWORKDAYS returns the number of whole working days between two dates, automatically excluding weekends (and optional holidays).
Question 7: A formula returns the error #NAME?. What is the most likely cause?
- A circular reference exists
- A function name is misspelled or unrecognized (Correct answer)
- The formula references an empty cell
- A number is divided by zero
Correct 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.
MOS Excel: Formulas and Functions 5
What does the SUMPRODUCT function do?