absolute references
2 sub-questions across 1 paper, 10 marks total.
Computer Applications Technology 2026
Convert the micro-grants in column G (GrantUSD) to ZAR at R19.20 per $. In B4, calculate the total Rand value of column G using the rate in B2. Format B4 as South African currency.
Show answerHide answer
Make sure B2 contains the rate 19.20.
In B4 type the formula above (data range as in your sheet). Using
=SUM(G8:G30)*$B$2is also correct.Select B4 → Ctrl+1 → Currency → Symbol R English (South Africa), 2 decimals → OK.
B2: 19.2 (the rate, if not already typed)
B4: =SUM(G8:G30)*B2B2: 19.2 (the rate, if not already typed)
B4: =SUM(G8:G30)*B2Why this earns the marks
the marks cover: using a formula (not typing a total), summing column G, multiplying by the rate, referring to B2 instead of typing 19.2 in the formula, and formatting as Rand currency — five genuinely distinct actions chained into one cell. Referring to B2 means that if the rate changes, the result updates: this is the core reason instruction 13 ("use formulas for every calculation") exists at all — a typed, hard-coded number is a frozen snapshot that silently goes stale the moment its inputs change, while a formula referencing B2 recalculates automatically. Alternative: =SUMPRODUCT(G8:G30,B2), which multiplies every value in the range by the rate and adds the results in one step — mathematically equivalent to SUM-then-multiply, just computed in a different order.
In D10, use a Lookup function to fetch the correct stipend amount for the start-up in that row from the Costs worksheet. The formula must copy down correctly.
Example, assuming the lookup key (e.g. start-up name or stage) is in A10 on this sheet and the Costs sheet has keys in A and stipends in B:
Show answerHide answer
Example, assuming the lookup key (e.g. start-up name or stage) is in A10 on this sheet and the Costs sheet has keys in A and stipends in B:
Or with XLOOKUP / INDEX-MATCH:
Look at the Costs sheet to see which column holds the key and which holds the stipend.
Choose the cell in row 10 whose value matches that key as the lookup value (relative, no $).
Lock the Costs table with $ signs, set the correct column number, and use FALSE for exact match.
Press Enter, then double-click the fill handle (or drag) to copy down.
Save and close 3_Incubator.xlsx.
D10: =VLOOKUP(A10,Costs!$A$2:$B$20,2,FALSE)D10: =VLOOKUP(A10,Costs!$A$2:$B$20,2,FALSE)=XLOOKUP(A10,Costs!$A$2:$A$20,Costs!$B$2:$B$20)
=INDEX(Costs!$B$2:$B$20,MATCH(A10,Costs!$A$2:$A$20,0))=XLOOKUP(A10,Costs!$A$2:$A$20,Costs!$B$2:$B$20)
=INDEX(Costs!$B$2:$B$20,MATCH(A10,Costs!$A$2:$A$20,0))Why this earns the marks
five marks: right lookup function, right lookup value, table on the Costs sheet, correct column index, exact match, with absolute references so it copies down — each is a separate, independently gradeable piece of the formula. When copied, the lookup value moves down one row each time (relative), but the Costs table must stay put (absolute): the whole point of a lookup table is that every row's formula searches the same reference table for its own key, so the key must shift with each row while the table itself must not. If the table was not locked, the range would slide down and eventually return #N/A, because a few rows down the table reference would have drifted past the real data entirely, searching an empty or unrelated range. If Costs is arranged across columns instead of down, use HLOOKUP or XLOOKUP — the horizontal-lookup equivalents of the same underlying search-and-return idea.