MOS - Microsoft Office Specialist Excel: Formulas and Functions Questions and Answers — 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'?
- =SUMIF(C2:C100,"Gadget",D2:D100) (Correct answer)
- =SUM(D2:D100)
- =IF(C2:C100="Gadget",SUM(D2:D100))
- =COUNTIF(C2:C100,"Gadget")
Correct answer: =SUMIF(C2:C100,"Gadget",D2:D100)
The SUMIF function is designed to sum values in a range that meet a specific criterion. In this case, it sums the values in the range D2:D100 only where the corresponding cell in the range C2:C100 is equal to "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?
- =A1+B1
- =$A$1+$B$1
- =A2+B2 (Correct answer)
- =#REF!
Correct answer: =A2+B2
By default, Excel uses relative cell references. When a formula with relative references (like A1 and B1) is copied to another cell, the references adjust relative to the new location. Copying the formula one row down changes the row numbers from 1 to 2, resulting in `=A2+B2`.
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?
- =UPPER(A2, B2)
- =CONCATENATE(A2," - Due: ",B2) (Correct answer)
- =SUM(A2,B2)
- =LEFT(A2, 4) & RIGHT(B2, 4)
Correct answer: =CONCATENATE(A2," - Due: ",B2)
The CONCATENATE function (or its modern replacement CONCAT, or the ampersand '&' operator) is used to join several text strings into one string. It allows for the combination of cell values and literal text strings, such as ' - Due: ', to create the desired output.
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?
- &B&5
- $B$5 (Correct answer)
- B#5
- ABS(B5)
Correct answer: $B$5
An absolute cell reference in Excel is created by placing a dollar sign ($) before both the column letter and the row number. This 'locks' the reference, so it does not change when the formula is copied or filled into other cells.
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?
- =VLOOKUP(F2, A1:D50, "C", FALSE)
- =HLOOKUP(F2, A1:D50, 3, FALSE)
- =VLOOKUP(F2, A1:D50, 3, FALSE) (Correct answer)
- =LOOKUP(F2, C1:C50, A1:A50)
Correct answer: =VLOOKUP(F2, A1:D50, 3, FALSE)
The VLOOKUP function searches for a value in the first column of a table array and returns a value in the same row from a specified column. The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Here, 3 is the correct column index number for column C, and FALSE specifies an exact match.
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?
- =PMT(B1, B2*12, B3)
- =PMT(B1/12, B2, -B3)
- =PMT(B1/12, B2*12, B3) (Correct answer)
- =PMT(B1, B2, B3/12)
Correct answer: =PMT(B1/12, B2*12, B3)
The PMT function calculates the payment for a loan. Its syntax is PMT(rate, nper, pv, ...). Since the payment is monthly, the annual interest rate (rate) must be divided by 12, and the loan term in years (nper) must be multiplied by 12 to get the total number of monthly periods.
MOS - Microsoft Office Specialist Excel: Formulas and Functions Questions and Answers
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'?