Microsoft Excel Formulas and Functions Questions and Answers 1 — 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?
- =SUMIF(A:A, "Electronics", B:B) (Correct answer)
- =SUM(B:B, A:A, "Electronics")
- =VLOOKUP("Electronics", A:B, 2, FALSE)
- =COUNTIF(A:A, "Electronics")
Correct answer: =SUMIF(A:A, "Electronics", B:B)
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?
- =A2 & " " & B2
- =CONCATENATE(A2, " ", B2)
- =TEXTJOIN(" ", TRUE, A2, B2) (Correct answer)
- =MERGE(A2, " ", B2)
Correct answer: =TEXTJOIN(" ", TRUE, A2, B2)
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?
- The formula will return an error if no match is found.
- The formula will look for an approximate match.
- The formula will search in the third row of the table array.
- The formula will only look for an exact match to the lookup value. (Correct answer)
Correct answer: The formula will only look for an exact match to the lookup value.
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?
- =C2*D2+$G$1
- =(C2*D2)*$G1
- =C2*$G$1 (Correct answer)
- =C2*G1
Correct answer: =C2*$G$1
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!?
- IF
- ISERROR
- IFERROR (Correct answer)
- VALIDATE
Correct answer: IFERROR
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.
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?
- =COUNT(B2:B50, 100)
- =COUNTIF(B2:B50, "=100") (Correct answer)
- =SUM(B2:B50, 100)
- =IF(B2:B50=100, COUNT)
Correct answer: =COUNTIF(B2:B50, "=100")
The COUNTIF function is used to count the number of cells within a range that meet a given criterion. The syntax is `COUNTIF(range, criteria)`. In this scenario, the range is B2:B50 and the criterion is "=100".
Microsoft Excel Formulas and Functions Questions and Answers 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?