ITPEC IP October 2024 Question 20

Topic: SoftwareAlso asked in October 2019 and April 2022

The tax-inclusive prices of products A and B are calculated by using two (2) tax rates that are set in cells D2 and E2 below. The correct expression that should be entered in cell D4 is $B4*(1.0+D$2), but a wrong expression $B4*(1.0+$D$2) was entered. When the expression that was entered in cell D4 is copied to cells D5, E4, and E5, which of the following is the numeric value that is displayed in cell E5?

ABCDE12345Tax rate 1Tax rate 2Tax rate0.050.1Product nameTax-exclusivepriceTax-inclusiveprice 1Tax-inclusiveprice 2Product A500Product B600

Explanation

630 — copied to E5, the wrong expression becomes $B5*(1.0+$D$2), which still uses the 5% rate in D2.

A $ fixes the column or row written right after it; the parts without $ shift when the expression is copied. In $B4*(1.0+$D$2):

  • •$B4 has column B fixed and row 4 relative, so copied one row down it becomes $B5, the 600 of product B
  • •$D$2 has both column and row fixed, so every copy still uses D2, the 0.05 rate

E5 therefore shows 600 × (1.0 + 0.05) = 630. The intended $B4*(1.0+D$2) would shift D to E in column E and give 660.

Answer (c)

Why not others:

  • •(a) 525 — 500 × 1.05, the value in D4; it ignores that the row of $B4 moves to 5 when copied down
  • •(b) 550 — 500 × 1.1, product A at the 10% rate: E4 under the intended expression, wrong in both row and rate
  • •(d) 660 — 600 × 1.1, what E5 would show with the intended D$2; the entered $D$2 keeps the rate at D2

Key rule: A $ freezes only what follows it: $B4 keeps column B but lets the row move, D$2 keeps row 2 but lets the column move, and $D$2 fixes the cell completely.

Software · AI-written, may contain errors

Practice this exam

This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official IP past-paper collection or Report an issue.