This set covers all of spreadsheet formulas: relative references, absolute references, logical functions, lookups and diagnosis.
Questions run from easy to harder. Cover each answer, write your own, then compare. All sheets, records and people are invented.
Questions
Q1 (easy). =A2*B2 is in C2. What is the formula after copying to C5?
Show answer
Three rows down, so both references move three rows: =A5*B5.
Q2 (easy). =B2+C2 is in D2. What is it after copying to F4?
Show answer
Two columns right and two rows down. B becomes D, C becomes E, and 2 becomes 4: =D4+E4.
Q3 (easy). Cell H1 holds 0.10. =D2*$H$1 is in E2, and D2 is 250. What does E2 show, and what does E3 show if D3 is 80?
Show answer
E2: 250 × 0.10 = 25. Copied to E3 the formula is =D3*$H$1, so 80 × 0.10 = 8.
Q4 (medium). A student writes =D2*H1 in E2 and copies it down. What does E3 contain, and what does it show?
Show answer
E3 contains =D3*H2. H2 is empty, so E3 shows 0. The rate needed to be locked: =D2*$H$1.
Q5 (medium). =IF(B2>=40,"Pass","Fail") is applied to marks 40 and 39. What is returned?
Show answer
40 returns Pass, since >= includes 40. 39 returns Fail.
Q6 (medium). Bands: 80 or more is A, 60 or more is B, otherwise C. Write one formula for B2 and give the result for 85, 60 and 59.
Show answer
=IF(B2>=80,"A",IF(B2>=60,"B","C")). 85 gives A, 60 gives B, 59 gives C. The strictest test comes first.
Q7 (medium). The rule is “mark at least 40 AND attendance at least 75”. Write the formula, then give the result for a mark of 52 and attendance of 70.
Show answer
=IF(AND(B2>=40,C2>=75),"Pass","Fail"). The mark passes but attendance does not, and AND needs both, so the result is Fail.
Q8 (medium). The table in F2:H4 holds P01 Pen 1.50, P02 Ruler 2.25 and P03 Notebook 3.20. Write a formula to return the price for the code in A2, then give the cost of 5 items coded P03.
Show answer
=VLOOKUP(A2,$F$2:$H$4,3,FALSE) returns 3.20 for P03. Cost: 5 × 3.20 = 16.00.
Q9 (harder). =VLOOKUP("P09",$F$2:$H$4,3,FALSE) returns #N/A. List two likely causes.
Show answer
P09 is not in the first column, so exact match fails. Other causes: a typing error or extra space in the code, or text compared with a number. Check the spelling of the code and the table first.
Q10 (harder). Sales in B2:B5 are 120, 95, 140 and 105, with =SUM(B2:B5) in B6. =B2/B6 copied down gives #DIV/0! from C3. Give the corrected formula and the value in C4.
Show answer
The divisor moved. Use =B2/B$6. The total is 460, so C4 is 140 ÷ 460 = 30.4% (to one decimal place).
Q11 (harder). Quantity 6 of code P03 is in B2, the code is in A2, and the service charge rate 0.06 is in J1. Write a formula for the total including the charge, then give the value.
Show answer
=B2*VLOOKUP(A2,$F$2:$H$4,3,FALSE)*(1+$J$1). Price 3.20 × 6 = 19.20. With 6% added: 19.20 × 1.06 = 20.352, which is 20.35 to two decimal places.
If you got these wrong
| Error | Go to |
|---|---|
| Wrong copied reference (Q1, Q2) | Build a formula with correct relative references |
| Zero from a moved rate (Q3, Q4) | Use an absolute reference deliberately |
| Boundary or AND/OR slips (Q5, Q6, Q7) | Choose an appropriate logical function |
Wrong column or #N/A (Q8, Q9) | Use a lookup with clearly stated assumptions |
| Errors after copying (Q10, Q11) | Diagnose a formula copied into the wrong range |
Log every slip in the mistake log and retest it in a few days. Use the spreadsheet reference practice grid for more predictions.
A teacher watching you build a formula live can find slips that the answer key cannot, which is what online one-to-one ICT tuition offers.