ITPEC IP April 2024 Question 20

Topic: SoftwareAlso asked in Spring 2020 and October 2022

Sales data by month for each branch is evaluated. In the “Evaluation” field for each branch by month, if the amount of sales at the branch is below the average sales amount of branches A through C for the month, the symbol “X” is to be displayed, and if the amount is above or equal to the average, the symbol “O” is to be displayed. Which of the following is an appropriate expression to be entered in cell C3? Here, the expression that is entered in cell C3 is copied and used in cell D3, cell E3, cells C5 through E5, and cells C7 through E7.

ABCDE1234567MonthItemBranch ABranch BBranch CJulySales amountEvaluation1,5001,0003,000AugustSales amountEvaluation1,2001,0001,000SeptemberSales amountEvaluation1,7001,5001,300a)Unit: million yen

Explanation

C2 left relative, the average range locked to columns C to E, X when below — the evaluated cell follows every copy; the average always covers branches A to C.

C3 judges Branch A's July sales (C2) against the July average. The formula is copied right to D3 and E3 (branches B and C) and down to rows 5 and 7 (August and September), so check each reference both ways:

  • •The evaluated cell moves both ways: C2 for C3, D2 for D3, C4 for C5. It takes no $: C2.
  • •The average range moves down with the month (row 2, 4, 6) but must always span columns C to E, so its columns are locked: $C2:$E2. In D3 it stays AVERAGE($C2:$E2); in C5 it becomes AVERAGE($C4:$E4).
  • •Below the average shows X, so the IF returns 'X' when the condition is true and 'O' otherwise.

July check: the average is (1,500 + 1,000 + 3,000) ÷ 3 ≈ 1,833, so branches A and B show X and branch C shows O.

Answer (d)

Why not others:

  • •(a) — $C2 locks column C, so D3 and E3 would still judge Branch A's figure, while the unlocked C2:E2 slides to D2:F2; and 'O' and 'X' are swapped
  • •(b) — the X and O are in the right order, but the references fail as in (a): D3 would compare C2 with the average of D2:F2
  • •(c) — the references are right, but the results are reversed: below-average sales show O

Key rule: Ask of each reference whether it must move when copied across and when copied down; put $ only before the column letter or row number that must stay fixed.

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.