Microsoft Excel Formulas and Functions Application Questions and Answers

Question 1

A sales manager has a worksheet containing sales data.
Column A lists the product names, and Column B lists the corresponding sales amounts.
The manager wants to find the total sales for 'Laptops' only.
Which of the following formulas is the most appropriate to accomplish this?

Accepted Answer

=SUMIF(A:A, "Laptops", B:B)

Answer

=COUNTIF(A:A, "Laptops")

Answer

=SUM(B:B)

Answer

=VLOOKUP("Laptops", A:B, 2, FALSE)

Question 2

You are creating a report to calculate commissions. In cell C2, you have a formula that divides the total profit (A2) by the number of units sold (B2). However, if a product has zero units sold (B2=0), the formula results in a #DIV/0! error. Which formula should be used in C2 to display the text 'N/A' instead of the error, while still performing the division if B2 is not zero?

Accepted Answer

=IFERROR(A2/B2, "N/A")

Answer

=IF(B2=0, A2/B2, "N/A")

Answer

=ISERROR(A2/B2, "N/A")

Answer

=DIVIDE(A2, B2, "N/A")

Question 3

A worksheet tracks employee training. Column D contains the 'Training Completion Date' and Column E contains the 'Certification Expiration Date'. You need to create a formula in Column F that displays 'Eligible' if an employee's training is complete (Column D is not blank) AND their certification has not expired (Column E is greater than today's date). Otherwise, it should display 'Not Eligible'. Which formula correctly implements this logic?

Accepted Answer

=IF(AND(D2<>"", E2>TODAY()), "Eligible", "Not Eligible")

Answer

=IF(OR(D2<>"", E2>TODAY()), "Eligible", "Not Eligible")

Answer

=IF(D2<>"" AND E2>TODAY(), "Eligible", "Not Eligible")

Answer

=IF(NOT(D2="", E2<=TODAY()), "Eligible", "Not Eligible")

Question 4

In a table of student grades, you want to count how many students scored 90 or higher. The scores are located in the range C2:C50. What is the correct formula to find this count?

Accepted Answer

=COUNTIF(C2:C50, ">=90")

Answer

=COUNT(C2:C50, ">90")

Answer

=SUMIF(C2:C50, ">=90")

Answer

=IF(C2:C50>=90, COUNT)

Question 5

Which of the following functions would you use to look up an employee's salary from a table, where employee IDs are in the first column and their corresponding salaries are in the third column?

Accepted Answer

VLOOKUP

Answer

HLOOKUP

Answer

INDEX

Answer

MATCH

Question 6

You need to combine the first name from cell A2 and the last name from cell B2 into a single cell, C2, formatted as 'Lastname, Firstname'. Which formula would achieve this?

Accepted Answer

=CONCAT(B2, ", ", A2)

Answer

=A2 & ", " & B2

Answer

=COMBINE(B2, ", ", A2)

Answer

=TEXTJOIN(" ", TRUE, A2, B2)