GMetrix Excel: Formulas and Functions Questions and Answers

Question 1

A user has a list of sales figures in column B, starting from B2.
In cell C2, they want to display the word "Bonus" if the sales figure in B2 is greater than $5,000, and "No Bonus" otherwise.
Which of the following formulas should be entered in cell C2 to achieve this?

Accepted Answer

=IF(B2>5000, "Bonus", "No Bonus")

Answer

=IF(B2<5000, "Bonus", "No Bonus")

Answer

=BONUS(B2>5000)

Answer

=IF(B2>5000, No Bonus, Bonus)

Question 2

You have the formula =SUM(A1:A10) in cell A11. If you copy this cell and paste it into cell C11, what will the formula in cell C11 be?

Accepted Answer

=SUM(C1:C10)

Answer

=SUM(A1:A10)

Answer

=SUM($A$1:$A$10)

Answer

The formula will result in an error.

Question 3

In a worksheet, you want to copy the formula =B4*C4 from cell D4 down to cell D10. However, you need the reference to cell C4 to remain constant in all the copied formulas, while the reference to B4 should adjust for each row. Which formula should you enter in D4 before copying?

Accepted Answer

=B4*C$4

Answer

=B4*$C4

Answer

=$B$4*$C$4

Answer

=B$4*$C$4

Question 4

A user wants to find the total sales for a specific product, "Widgets", listed in a sales report. Column A (A2:A100) contains the product names, and Column C (C2:C100) contains the corresponding sales amounts. Which function should be used to calculate the total sales for only the Widgets?

Accepted Answer

=SUMIF(A2:A100, "Widgets", C2:C100)

Answer

=COUNTIF(A2:A100, "Widgets")

Answer

=VLOOKUP("Widgets", A2:C100, 3, FALSE)

Answer

=SUM(C2:C100)

Question 5

When troubleshooting a formula in Excel, you see the '#NAME?' error in a cell. What is the most likely cause of this error?

Accepted Answer

Excel does not recognize text in the formula, such as a misspelled function name or an undefined named range.

Answer

The formula is attempting to divide by zero.

Answer

The formula contains a circular reference.

Answer

A referenced cell contains a value of the wrong data type.

Question 6

Which of the following functions correctly joins the text from cell A1 and cell B1, separated by a single space?

Accepted Answer

=A1&" "&B1

Answer

=JOIN(A1, " ", B1)

Answer

=A1+" "+B1

Answer

=TEXTJOIN(A1, B1, " ")