MOS - Microsoft Office Specialist Excel: Formulas and Functions Questions and Answers

Question 1

A financial analyst needs to calculate the total sales for a specific product, 'Gadget', from a sales report.
The product names are in column C (C2:C100) and the corresponding sales amounts are in column D (D2:D100).
Which of the following formulas will correctly calculate the total sales for only the product 'Gadget'?

Accepted Answer

=SUMIF(C2:C100,"Gadget",D2:D100)

Answer

=SUM(D2:D100)

Answer

=IF(C2:C100="Gadget",SUM(D2:D100))

Answer

=COUNTIF(C2:C100,"Gadget")

Question 2

You have a formula in cell C1: `=A1+B1`. You use the fill handle to drag this formula down to cell C2. What will the formula in cell C2 be?

Accepted Answer

=A2+B2

Answer

=A1+B1

Answer

=$A$1+$B$1

Answer

=#REF!

Question 3

A project manager is creating a task list. They have the task name in column A and the due date in column B. They want to create a combined text string in column C that reads 'Task Name - Due: [Date]'. For cell C2, with 'Design Mockup' in A2 and '3/15/2026' in B2, which function is best suited to join these elements with a separator?

Accepted Answer

=CONCATENATE(A2," - Due: ",B2)

Answer

=UPPER(A2, B2)

Answer

=SUM(A2,B2)

Answer

=LEFT(A2, 4) & RIGHT(B2, 4)

Question 4

To prevent a cell reference from changing when a formula is copied, you must use an absolute reference. Which of the following is a syntactically correct absolute reference to cell B5?

Accepted Answer

$B$5

Answer

&B&5

Answer

B#5

Answer

ABS(B5)

Question 5

A user needs to find the correct department for an employee by looking up their Employee ID in a table. The table array is in the range A1:D50, with Employee IDs in column A and Department names in column C. Which formula correctly finds the department for the Employee ID located in cell F2?

Accepted Answer

=VLOOKUP(F2, A1:D50, 3, FALSE)

Answer

=VLOOKUP(F2, A1:D50, "C", FALSE)

Answer

=HLOOKUP(F2, A1:D50, 3, FALSE)

Answer

=LOOKUP(F2, C1:C50, A1:A50)

Question 6

A user wants to calculate the monthly payment for a loan. They have the annual interest rate in cell B1 (6%), the loan term in years in cell B2 (5), and the loan amount (present value) in cell B3 ($25,000). Which formula correctly calculates the monthly payment?

Accepted Answer

=PMT(B1/12, B2*12, B3)

Answer

=PMT(B1, B2*12, B3)

Answer

=PMT(B1/12, B2, -B3)

Answer

=PMT(B1, B2, B3/12)