ITPEC IP October 2018 Question 17
As shown in the spreadsheet below, three types of examinations were conducted. Each type of examination has a different perfect score. Each resulting score is converted to a value assuming 100 points as the perfect score, and displayed in cells F3 through H10. Which of the following is the expression to be entered in cell F3? Here, the expression in cell F3 is copied to cells F3 through H10.
Explanation
B3 relative, B$2 with the row fixed — 100*B3/B$2 lets the score follow each student and subject while the perfect score stays in row 2.
Copying moves every reference by the same number of rows and columns; a $ pins the part it precedes.
- •The score must move both ways: right to C3 and D3 for Math and Science, and down to rows 4–10 for the other students. So B3 stays fully relative.
- •The perfect score must move right, B2 → C2 → D2, but never down: its row is fixed, B$2.
- •Check: H10 gets
100*D10/D$2, 100 × 108 ÷ 150 = 72, Susan Miller's Science score out of 100.
Answer (b)
Why not others:
- •(a) —
$B2pins the column and lets the row move: in F4 the divisor slides to$B3, John Mayer's 180, and in G3 it stays English's 250 instead of Math's 200 - •(c) —
$B3and$B2both pin column B, so G3 and H3 still compute the English score, and one row down the divisor slides to$B3 - •(d) —
B$3pins the score to row 3, so every row repeats John Mayer's result: F4 computes100*B$3/B$2again
Key rule: A $ before the column letter keeps the column when the formula is copied across; a $ before the row number keeps the row when it is copied down. Fix only the direction in which the reference must not move.
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.