MOS Excel: Formulas and Functions 5

Question 1

What does the SUMPRODUCT function do?

Accepted Answer

Multiplies corresponding elements of arrays and returns their sum

Answer

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?

Accepted Answer

=A1*$C$1

Answer

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?

Accepted Answer

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

Answer

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?

Accepted Answer

MATCH(MIN(...),...)

Answer

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?

Accepted Answer

$1,234.50

Answer

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.

MOS - Microsoft Office Specialist Certification Practice Test