Confident Cells

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.

Your turn

In D2, work out each pupil's residual: their actual grade minus their target grade. A pupil above target should get a positive number.

ABCD
1PupilTargetActualResidual
2Evie Jones78
3Ruby Wood55
4Freya Evans79
5Kacper Green53
6Fatima Taylor68
7Reuben Walker56
8Esme Hall76
9Poppy Wilson65
10Aisha Brown33
11Finley Wood55
12Callum Jones76
13Amira Hall54
14Maya Kowalski41
15Leah Price34
16Oliver Shah76
17Zainab Wood57
18Sami Johnson63
19Tyrese Okafor43
20Hana Bennett31
21Ellie Green65

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

ABCDEFG
1PupilClassTargetActualResidualPPCompared
2Ana10A56No
3Bilal10A64Yes
4Chloe10B44No
5Dan10B75Yes
  1. =D2-C2 in E2. Ana is 1 grade above target; Bilal and Dan are −2, two grades below.
  2. =IF(D2>C2,"Above",IF(D2=C2,"On","Below")) in G2. Ana is Above, Chloe is On, and the others are Below.
  3. =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 AVERAGEIF shows 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

  1. In D2, work out each pupil's residual: their actual grade minus their target grade. A pupil above target should get a positive number.
  2. In D2, show Above, On or Below to compare each pupil's actual grade with their target.
  3. 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.
  4. In H1, count how many pupils are 2 or more grades below their target.
  5. 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.