Fahdi has autofilled a formula down in the Discount column to show the discount for each Member Type but is getting an error message. What is the problem?

Question

BlackTom AI · Accepted Answer

A relative cell reference shifts when you autofill, so the lookup range moves down and the formula breaks — absolute references are required.

Relevant concepts:
- Relative vs absolute references: A reference like B2 is relative and changes when copied; $B$2 is absolute and stays fixed.
- When using a lookup table (e.g., VLOOKUP or direct cell references to the discount table), the table/range must be fixed with absolute references so every copied formula refers to the same discount table.

Step-by-step reasoning:
1. Fahdi’s Discount column is intended to pull the percentage from the small table (Gold = 10%, Others = 5%). The initial formula in the first data row probably referenced that table (for example: =VLOOKUP(A12,B2:C3,2,FALSE) or =IF(A12="Gold",C2,IF(A12="Others",C3,"")) ).
2. Because the formula used relative references (B2:C3 or C2/C3 without $), autofilling downward changes those references for each row. For example:
   - Row 1 formula (relative): =VLOOKUP(A12,B2:C3,2,FALSE)
   - After autofill to next row it becomes: =VLOOKUP(A13,B3:C4,2,FALSE)
3. B3:C4 no longer contains the lookup table headers/data in the expected place; the lookup either returns an error, returns a wrong cell (like the text "Fee" from a header), or produces a wildly incorrect percentage (e.g., 25000%) because the formula is reading different cell values or dividing/multiplying unexpected numbers.
4. Using absolute references fixes this: =VLOOKUP(A12,$B$2:$C$3,2,FALSE) or using $C$2/$C$3 in direct references ensures every copied formula references the same discount table cells.

Why the correct answer is (a):
- The pattern of errors in the screenshot (some percent values, one cell showing the word “Fee”, others showing huge percentages) is typical when the lookup range or referenced cells shift due to relative references while autofilling. Absolute references would prevent the range from moving.

Why the other choices are incorrect:
- (b) Wrong mathematical operator: the issue isn’t an operator like + vs *; errors shown are lookup/reference errors, not simple arithmetic sign mistakes.
- (c) Used a formula instead of a function: a “formula vs function” distinction is irrelevant — both are formulas; the problem is reference behavior.
- (d) Formula referencing the wrong cell: superficially true for rows after autofill, but the underlying cause is that the references were not made absolute. The best diagnosis is that absolute references were not used.

Conclusion: The problem is that Fahdi did not use absolute references in his formula (answer a).

Fahdi has autofilled a formula down in the Discount column to show the discount for each Member Type but is getting an error message. What is the problem?单项选择题

类似问题