ITPEC IP April 2025 Question 20
Spreadsheet software is to be used to perform a calculation with the monthly sales data for each product shown in the worksheet below. When the expression “COUNTIF(B2:D2,>15000)” is entered in cell E2 and then copied to cell E3 and cell E4, which of the following is the value that is displayed in cell E4?
Explanation
2 — copied down to E4, the relative range becomes B4:D4, and two of Product C's three figures exceed 15,000.
The range B2:D2 has no $ signs, so it is a relative reference: copying E2 two rows down shifts it two rows down as well.
- •E2:
COUNTIF(counts 20,000 → 1 (15,000 is not greater than 15,000)B2: D2, >15000) - •E3:
COUNTIF(finds nothing above 15,000 → 0B3: D3, >15000) - •E4:
COUNTIF(counts 20,000 and 30,000 but not 10,000 → 2B4: D4, >15000)
Answer (c)
Why not others:
- •(a) 0 — the value of E3 (Product B), the wrong row
- •(b) 1 — the value of E2 (Product A); it is what E4 would show if the range stayed B2:D2 instead of shifting
- •(d) 3 — counts every cell of Product C, including 10,000, which fails the condition
Key rule: A relative reference moves with the copy by the same number of rows and columns, and ">" is strict, so a value equal to the threshold is not counted.
Software · AI-written, may contain errors
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.