IPA IP 2020 Question 71
A question IPA Japan published in 令和2年度 10月 ITパスポート試験 公開問題 問71, translated into English. ITPEC is based on the same exams.
Same question on ITPEC: Apr 2025 Q20 +2 more
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 the past questions IPA Japan publishes, translated into English by ITPEC Practice. ITPEC Practice is an independent study tool and is not affiliated with IPA or ITPEC. See the original paper (PDF) or Report an issue.