Microsoft Excel Formulas and Functions Questions and Answers

Question 1

A sales manager has a worksheet containing sales data.
Column A lists the product category, and Column B lists the sales amount.
Which formula correctly calculates the total sales for the 'Electronics' category?

Accepted Answer

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

Answer

=SUM(B:B, A:A, "Electronics")

Answer

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

Answer

=COUNTIF(A:A, "Electronics")

Question 2

A user wants to combine the first name from cell A2 and the last name from cell B2, separated by a space. Which of the following formulas is the most efficient and modern way to achieve this, especially if needing to handle many text strings and a consistent delimiter?

Accepted Answer

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

Answer

=A2 & " " & B2

Answer

=CONCATENATE(A2, " ", B2)

Answer

=MERGE(A2, " ", B2)

Question 3

In the formula `=VLOOKUP(A1, D1:F20, 3, FALSE)`, what does the `FALSE` argument signify?

Accepted Answer

The formula will only look for an exact match to the lookup value.

Answer

The formula will return an error if no match is found.

Answer

The formula will look for an approximate match.

Answer

The formula will search in the third row of the table array.

Question 4

A financial analyst created the formula `=C2*D2` in cell E2 to calculate the total cost. They want to copy this formula down to cell E10. However, the tax rate used in the calculation is in a fixed cell, G1. Which formula should be entered in E2 before copying it down to ensure the tax rate reference does not change?

Accepted Answer

=C2*$G$1

Answer

=C2*D2+$G$1

Answer

=(C2*D2)*$G1

Answer

=C2*G1

Question 5

Which of the following functions is specifically designed to return a custom value or message when a formula results in an error, such as #N/A or #DIV/0!?

Accepted Answer

IFERROR

Answer

IF

Answer

ISERROR

Answer

VALIDATE

Question 6

You have a list of employee scores in column B (from B2 to B50). You need to find out how many employees scored exactly 100. Which is the correct formula to use?

Accepted Answer

=COUNTIF(B2:B50, "=100")

Answer

=COUNT(B2:B50, 100)

Answer

=SUM(B2:B50, 100)

Answer

=IF(B2:B50=100, COUNT)

A sales manager has a worksheet containing sales data.Column A lists the product category, and Column B lists the sales amount.Which formula correctly calculates the total sales for the 'Electronics' category?

A sales manager has a worksheet containing sales data.
Column A lists the product category, and Column B lists the sales amount.
Which formula correctly calculates the total sales for the 'Electronics' category?