Target vs actual and residuals: practice exercises
Residuals, above or below target, class averages, the PP gap. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In D2, work out each pupil's residual: their actual grade minus their target grade. A pupil above target should get a positive number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Pupil | Target | Actual | Residual |
| 2 | Evie Jones | 7 | 8 | |
| 3 | Ruby Wood | 5 | 5 | |
| 4 | Freya Evans | 7 | 9 | |
| 5 | Kacper Green | 5 | 3 | |
| 6 | Fatima Taylor | 6 | 8 | |
| 7 | Reuben Walker | 5 | 6 | |
| 8 | Esme Hall | 7 | 6 | |
| 9 | Poppy Wilson | 6 | 5 | |
| 10 | Aisha Brown | 3 | 3 | |
| 11 | Finley Wood | 5 | 5 | |
| 12 | Callum Jones | 7 | 6 | |
| 13 | Amira Hall | 5 | 4 | |
| 14 | Maya Kowalski | 4 | 1 | |
| 15 | Leah Price | 3 | 4 | |
| 16 | Oliver Shah | 7 | 6 | |
| 17 | Zainab Wood | 5 | 7 | |
| 18 | Sami Johnson | 6 | 3 | |
| 19 | Tyrese Okafor | 4 | 3 | |
| 20 | Hana Bennett | 3 | 1 | |
| 21 | Ellie Green | 6 | 5 |
How it works
A residual is actual minus target, so a pupil above target has a positive residual. Averaging residuals shows how a class or a group did against expectations.
AVERAGEIF(range, criterion, average_range) averages only the rows that match, such as one class or the pupils marked "Yes" for pupil premium.
=D2-C2 =IF(D2>C2,"Above",IF(D2=C2,"On","Below")) =AVERAGEIF(B2:B5,"10A",D2:D5) =COUNTIF(E2:E5,"<=-2")
Residuals
A residual is actual minus target. Get the order right: actual first, so a pupil above target has a positive residual and one below has a negative one.
A nested IF turns the comparison into words: test "above" first, then "on", and anything left must be "below".
Worked example
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Pupil | Class | Target | Actual | Residual | PP | Compared |
| 2 | Ana | 10A | 5 | 6 | No | ||
| 3 | Bilal | 10A | 6 | 4 | Yes | ||
| 4 | Chloe | 10B | 4 | 4 | No | ||
| 5 | Dan | 10B | 7 | 5 | Yes |
=D2-C2in E2. Ana is 1 grade above target; Bilal and Dan are −2, two grades below.=IF(D2>C2,"Above",IF(D2=C2,"On","Below"))in G2. Ana is Above, Chloe is On, and the others are Below.=AVERAGEIF(B2:B5,"10A",D2:D5)in G7. AVERAGEIF averages only the matching rows: 10A's actual grades average 5.
Averages and gaps
- Averaging residuals by class with
AVERAGEIFshows which classes did better or worse than expected. - To count pupils 2 or more below target, look for residuals of −2 or less:
COUNTIF(residuals,"<=-2"). - A gap is one group's average minus another's, such as pupil premium "Yes" minus "No".
What you'll practise
- In D2, work out each pupil's residual: their actual grade minus their target grade. A pupil above target should get a positive number.
- In D2, show Above, On or Below to compare each pupil's actual grade with their target.
- In I2, work out the average residual for the class named in H2. Your formula will be copied down to I5 for the other classes.
- In H1, count how many pupils are 2 or more grades below their target.
- In H1, work out the pupil premium gap: the average residual of pupil premium pupils (Yes in column E) minus the average residual of everyone else. A negative gap means PP pupils did less well against target.