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

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?

Accepted Answer

=SUM(C1:C10)

Answer

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?

Accepted Answer

=B4*C$4

Answer

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?

Accepted Answer

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

Answer

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?

Accepted Answer

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

Answer

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.

GMetrix Exam Practice Test

GMetrix Exam Practice Test

GMetrix Excel: Formulas and Functions Questions and Answers