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

The SUMIF function is designed to sum values in a range that meet a specific criterion. In this case, it sums the values in column B where the corresponding value in column A is '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

While CONCATENATE and the '&' operator work, TEXTJOIN is the most powerful and modern function for this task. It allows you to specify a delimiter (a space, in this case) once and can optionally ignore empty cells, making it more efficient for larger ranges.

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

In a VLOOKUP formula, the final argument (`range_lookup`) determines the match type. `FALSE` specifies that VLOOKUP must find an exact match for the lookup value. If an exact match is not found, it will return an #N/A error.

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

Using dollar signs ($) creates an absolute reference. `$G$1` locks both the column 'G' and the row '1', so when the formula is copied down, the reference to cell G1 remains constant, while the relative reference C2 will adjust to C3, C4, etc.

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

The IFERROR function evaluates a formula and returns a specified value if the formula results in an error. Otherwise, it returns the result of the formula itself. This is the standard way to trap and handle errors gracefully.

Excel Practice Test

Excel Practice Test

Microsoft Excel Formulas and Functions Questions and Answers