ITPEC IP April 2019 Question 20
A spreadsheet program is used to create a quick reference chart for the compound interest per a principal of 1,000 dollars. Which of the following is the expression that should be entered in cell B4? Here, the expression in cell B4 is copied to each of cells B4 through F13.
Explanation
B$2 for the rate, $A4 for the years — the rate is in row 2 of each column, and the number of years in column A of each row.
The interest after n years is principal × (1 + r)n − 1. Copying B4 to every cell of B4:F13 moves the formula both right and down, so each reference must lock exactly what should not move:
- •principal B1: the same cell everywhere, so both are locked:
$B$1 - •rate r: changes by column (B to F) but always sits in row 2, so lock the row:
B$2 - •years n: changes by row (4 to 13) but always sits in column A, so lock the column:
$A4
The expression is $B$1*((1+B$2)^$A4−1).
Answer (c)
Why not others:
- •(a) —
$B2locks the column and frees the row, the wrong way round: every column uses the 0.02 rate, and copying down reads B3, B4 and so on - •(b) — both mixed references are the wrong way round:
$B2as in (a), andA$4stays on row 4 (1 year) and moves to B4, C4 when copied right - •(d) — the rate is right, but
A$4locks row 4, so every row uses 1 year, and copied right it reads B4, C4 instead of column A
Key rule: $ locks the column or row letter it stands before: a value laid out along a row needs the row locked (B$2), one laid out down a column needs the column locked ($A4).
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.