GMetrix Excel: Formulas and Functions Questions and Answers — 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?
- =IF(B2>5000, "Bonus", "No Bonus") (Correct answer)
- =IF(B2<5000, "Bonus", "No Bonus")
- =BONUS(B2>5000)
- =IF(B2>5000, No Bonus, Bonus)
Correct answer: =IF(B2>5000, "Bonus", "No Bonus")
The IF function is used for conditional logic. Its syntax is IF(logical_test, [value_if_true], [value_if_false]). The formula =IF(B2>5000, "Bonus", "No Bonus") correctly tests if the value in B2 is greater than 5000, returns the text "Bonus" if true, and "No Bonus" if false. Text values within a formula must be enclosed in double quotes.
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?
- =SUM(A1:A10)
- =SUM(C1:C10) (Correct answer)
- =SUM($A$1:$A$10)
- The formula will result in an error.
Correct answer: =SUM(C1:C10)
By default, Excel uses relative cell references. When a formula with relative references is copied to a new location, the references adjust relative to the new position. Since the formula was moved two columns to the right (from A to C), the column references inside the formula also shift two columns to the right, changing from A1:A10 to C1:C10.
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?
- =B4*C$4 (Correct answer)
- =B4*$C4
- =$B$4*$C$4
- =B$4*$C$4
Correct answer: =B4*C$4
This requires a mixed reference. Using a dollar sign ($) before a row number (C$4) locks the row, making it an absolute reference. The column reference (C) remains relative. The reference B4 is fully relative. When copied down, B4 will become B5, B6, etc., while C$4 will remain C$4, achieving the desired calculation.
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?
- =COUNTIF(A2:A100, "Widgets")
- =VLOOKUP("Widgets", A2:C100, 3, FALSE)
- =SUMIF(A2:A100, "Widgets", C2:C100) (Correct answer)
- =SUM(C2:C100)
Correct answer: =SUMIF(A2:A100, "Widgets", C2:C100)
The SUMIF function sums the values in a range that meet a specified criterion. The syntax is SUMIF(range, criteria, [sum_range]). Here, it checks range A2:A100 for the criteria "Widgets" and then sums the corresponding values from the sum_range 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?
- The formula is attempting to divide by zero.
- The formula contains a circular reference.
- Excel does not recognize text in the formula, such as a misspelled function name or an undefined named range. (Correct answer)
- A referenced cell contains a value of the wrong data type.
Correct answer: Excel does not recognize text in the formula, such as a misspelled function name or an undefined named range.
The #NAME? error occurs when Excel doesn't recognize something in the formula's text. This is most commonly due to a typo in a function name (e.g., `VLLOKUP` instead of `VLOOKUP`) or a reference to a named range that does not exist or is misspelled.
Question 6: Which of the following functions correctly joins the text from cell A1 and cell B1, separated by a single space?
- =A1&" "&B1 (Correct answer)
- =JOIN(A1, " ", B1)
- =A1+" "+B1
- =TEXTJOIN(A1, B1, " ")
Correct answer: =A1&" "&B1
The ampersand (&) is a concatenation operator that joins text strings. The formula =A1&" "&B1 correctly takes the value from A1, joins it with a space character (which must be enclosed in double quotes), and then joins that with the value from B1. Functions like CONCAT or TEXTJOIN could also be used, but the ampersand is the most direct operator for this task.
GMetrix Excel: Formulas and Functions Questions and Answers
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?