Spreadsheet: 598828d1542730427-formula-containing-index-match-isnumber-and-search-with-a-few-ifs-thrown-in-not-working-data-checking
Sheet: Lookup

LeveltoNum Table (PttoNum table is B and C columns) |  |  |  |  |  |  | GradetoNum Table |  |  |  |  |  | NumtoGrade Table |  |  |  |  |  | Progression Table (BaseLine) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Progression Table (Aspirational) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Start Level KS2 | End KS4 Should Achieve
Level | Pt Score | Num | 2016 Fine Grade | Level Lookup |  | Grade | Pt score | Num | Lookup Pt |  |  | PttoGCSE | GcseNum | GCSE Pixl Grade | GCSE Grade | GCSE Grade 2017 |  | KS2 Level | Yr07 Aut | Yr07 Spr | Yr07 [NAME] | Yr08 Aut | Yr08 Spr | Yr08 Sum | Yr09 Aut | Yr09 Spr | Yr09 [NAME] | Yr10 Aut | Yr10 Spr | Yr10 [NAME] | [NAME] | Yr11 Spr | Yr11 Sum |  |  | KS2 Level | Yr07 Aut | Yr07 Spr | Yr07 [NAME] | Yr08 Aut | Yr08 Spr | Yr08 Sum | Yr09 Aut | Yr09 Spr | Yr09 [NAME] | Yr10 Aut | Yr10 Spr | Yr10 [NAME] | [NAME] | Yr11 Spr | Yr11 Sum |  | KS2 avg fine level (Eng & Math) | Attainment 8 2015 estimate | [NAME] 2016 | [NAME] 2017 | Note | GCSE Equiv | Old GCSE [NAME] | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  | Course Code | Course | Course Desc
1L | 7 | 0.666 |  | 1c |  | A* | 58 | 10 | 63 |  |  | 0 | 0 | U | U | 0 |  | LIN<L5 | 0 | 0.3333 | 0.6666 | 0.6666 | 1 | 1.3333 | 1.3333 | 1.6666 | 2 | 2 | 2.3333 | 2.6666 | 2.6666 | 3 | 3 |  |  | LIN<L5 | 0.3333 | 0.6666 | 1 | 1 | 1.3333 | 1.6666 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.3333 | 3.6666 | 4 |  | 1.5 | 13.01 |  | 1 | a | G | 3 | 0 |  | a. Pupils with mean KS2 fine grade score of <=1.5 are assigned a KS2 score of 1.5 |  |  |  |  |  |  |  |  |  |  | A2/GCSEF | Ar/GCSEF | Art course (2 year) (GCSEF)
1c | 7 | 0.666 |  | 1c |  | A1 | 54 | 9.333 | 59 |  |  | 13 | 1.66 | W | W | 0.5 |  | LIN=L5 | 0.3333 | 0.6666 | 1 | 1 | 1.33 | 1.6666 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.3333 | 3.6666 | 4 |  |  | LIN>=L5 | 0.3333 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.6666 | 4 | 4.3333 | 4.6666 | 5 |  | 2 | 17.57 |  | 1 | b | G | 3 | 1.6 |  | b. Pupils with mean KS2 fine grade score between 1.6 and 2.0 are assigned a KS2 score of 2.0 |  |  |  |  |  |  |  |  |  |  | A3/GCSEF | Ar/GCSEF | Art A side (2nd year) (GCSEF)
1 | 9 | 1 |  | 1b |  | A | 52 | 9 | 57 |  |  | 19 | 2.66 | G3 | G | 1 |  | LIN>L5 | 0.3333 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.6666 | 4 | 4.3333 | 4.6666 | 5 |  |  | Exp<L5 | 0 | 0.3333 | 0.6666 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.3333 | 3.6666 | 4 |  | 2.5 | 19.14 |  | 1 | c | G | 3 | 2.1 |  | c. Pupils with mean KS2 fine grade score between 2.1 and 2.5 are assigned a KS2 score of 2.5 |  |  |  |  |  |  |  |  |  |  | Ar/GCSEF | Ar/GCSEF | Art and Design (GCSEF)
1 | 9 | 1 |  | 1b |  | A3 | 50 | 8.666 | 55 |  |  | 21 | 3 | G2 | G | 1 |  | EXP<L5 | 0 | 0 | 0.3333 | 0.3333 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2 | 2.3333 | 2.6666 | 2.6666 | 3 | 3 |  |  | EXP>=L5 | 0 | 0.3333 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2.3333 | 3 | 3.3333 | 3.6666 | 4 | 4.3333 | 4.6666 | 5 |  | 2.8 | 20.11 |  | 1 | d | G | 4 | 2.6 |  | d. Pupils with mean KS2 fine grade score between 2.6 and 2.8 are assigned a KS2 score of 2.8 |  |  |  |  |  |  |  |  |  |  | As/GCSEF | As/GCSEF | Additional Science
1b | 9 | 1 |  | 1b |  | B1 | 48 | 8.333 | 53 |  |  | 23 | 3.33 | [NAME] | G | 1 |  | EXP=L5 | 0 | 0.3333 | 0.6666 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.3333 | 3.6666 | 4 |  |  | FRe<L5 | -1 | -0.6666 | -0.3333 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.3333 | 3.6666 | 4 |  | 2.9 | 21.62 |  | 1.5 |  | F | 4 | 2.9 |  | e. Pupils with mean KS2 fine grade score of >=5.8 are assigned a KS2 score of 5.8 |  |  |  |  |  |  |  |  |  |  | C2/GCSEF | Cd/GCSEF | Child dev (2nd y) (GCSEF)
1M | 9 | 1 |  | 1b |  | B | 46 | 8 | 51 |  |  | 25 | 3.66 | F3 | F | 1.5 |  | EXP>L5 | 0 | 0.3333 | 0.6666 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.6666 | 4 | 4.3333 | 4.6666 | 5 |  |  | FRe>=L5 | -1 | -0.6666 | -0.3333 | 1 | 1.3333 | 1.6666 | 2 | 2.3333 | 3 | 3.3333 | 3.6666 | 4 | 4.3333 | 4.6666 | 5 |  | 3 | 23.08 |  | 1.5 |  | F | 4 | 3 |  |  |  |  |  |  |  |  |  |  |  |  | Cd/GCSEF | Cd/GCSEF | Child Development (GCSEF)
1a | 11 | 1.333 |  | 1a |  | B3 | 44 | 7.666 | 49 |  |  | 27 | 4 | F2 | F | 1.5 |  | IMM<L5 | -1 | -1 | -0.6666 | 0.3333 | 0.6666 | 1 | 1.3333 | 2.3333 | 3 | 1.3333 | 2.3333 | 3 | 1.3333 | 2.3333 | 3 |  |  | MFL<L5 | 0 | 0 | 0 | 0 | 0.3333 | 0.6666 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.3333 | 3.6666 | 4 |  | 3.1 | 23.65 |  | 1.5 |  | F | 4 | 3.1 |  |  |  |  |  |  |  |  |  |  |  |  | Ci/GCSEF | Ci/GCSEF | Citizenship (GCSEF)
1U | 11 | 1.333 |  | 1a |  | C1 | 42 | 7.333 | 47 |  |  | 29 | 4.33 | F1 | F | 1.5 |  | IMM=L5 | -1 | -0.6666 | -0.3333 | 0.6666 | 1 | 1.3333 | 1.6666 | 3 | 4 | 1.6666 | 3 | 4 | 1.6666 | 3 | 4 |  |  | MFL>=L5 | 0 | 0 | 0 | 0.3333 | 0.6666 | 1 | 2 | 2.3333 | 3 | 3.3333 | 3.6666 | 4 | 4.3333 | 4.6666 | 5 |  | 3.2 | 24.16 |  | 1.5 |  | F | 4 | 3.2 |  |  |  |  |  |  |  |  |  |  |  |  | Cs/GCSEF | Cs/GCSEF | Science Combined (GCSEF)
2c | 13 | 1.666 |  | 2c |  | C | 40 | 7 | 45 |  |  | 31 | 4.66 | E3 | E | 2 |  | IMM>L5 | -1 | -0.6666 | -0.3333 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.6666 | 4 | 4.3333 | 4.6666 | 5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.3 | 25.88 |  | 1.5 |  | F | 4 | 3.3 |  |  |  |  |  |  |  |  |  |  |  |  | De/GCSEF | De/GCSEF | MFL German (GCSEF)
2L | 13 | 1.666 |  | 2c |  | [NAME] | 38 | 6.666 | 43 |  |  | 33 | 5 | E2 | E | 2 |  | MFL<L5 | -0.6666 | -0.6666 | -0.3333 | 0.3333 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2 | 2.3333 | 2.6666 | 2.6666 | 3 | 3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.4 | 27.42 |  | 1.5 |  | F | 5 | 3.4 |  |  |  |  |  |  |  |  |  |  |  |  | Dr/GCSEF | Dr/GCSEF | Drama (GCSEF)
2 | 15 | 2 |  | 2b |  | D1 | 36 | 6.333 | 41 |  |  | 35 | 5.33 | E1 | E | 2 |  | MFL=L5 | -0.6666 | -0.3333 | 0 | 0.6666 | 1 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.3333 | 3.6666 | 4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.5 | 28.27 |  | 2 |  | E | 5 | 3.5 |  |  |  |  |  |  |  |  |  |  |  |  | En/GCSEF | En/GCSEF | English (GCSEF)
2 | 15 | 2 |  | 2b |  | D | 34 | 6 | 39 |  |  | 37 | 5.66 | D3 | D | 3 |  | MFL>L5 | -0.6666 | -0.3333 | 0 | 1.3333 | 1.6666 | 2 | 2.3333 | 2.6666 | 3 | 3.3333 | 3.6666 | 4 | 4.3333 | 4.6666 | 5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.6 | 29.95 |  | 2 |  | E | 5 | 3.6 |  |  |  |  |  |  |  |  |  |  |  |  | Ex/Gen | En/GCSEF | Extra English Maths ([NAME])
2b | 15 | 2 |  | 2b |  | D3 | 32 | 5.666 | 37 |  |  | 39 | 6 | D2 | D | 3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.7 | 31.81 |  | 2 |  | E | 5 | 3.7 |  |  |  |  |  |  |  |  |  |  |  |  | Fo/GCSEF | Fo/GCSEF | DT Food (GCSEF)
2M | 15 | 2 |  | 2b |  | E1 | 30 | 5.333 | 35 |  |  | 41 | 6.33 | D1 | D | 3 |  | Term Expected Progress |  |  |  |  |  | Term Expected Progress (Aspirational) |  |  |  |  |  |  |  |  |  |  |  |  |  | 2017 Points |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.8 | 33.33 |  | 2 |  | E | 6 | 3.8 |  |  |  |  |  |  |  |  |  |  |  |  | Fr/GCSEF | Fr/GCSEF | MFL French (GCSEF)
2a | 17 | 2.333 |  | 2a |  | E | 28 | 5 | 33 |  |  | 43 | 6.66 | [NAME] | C | 4 |  | Term | Not L5 num | Not L5 Pts | [NAME] | L5 Points |  | Term | Not L5 num | Not L5 Pts |  | L5 Points |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.9 | 34.98 |  | 3 |  | D | 6 | 3.9 |  |  |  |  |  |  |  |  |  |  |  |  | G2/GCSEF | Gg/GCSEF | Geog (2yr) (GCSEF)
2U | 17 | 2.333 |  | 2a |  | E3 | 26 | 4.666 | 31 |  |  | 45 | 7 | C2 | C | 4 |  | [NAME] Yr7 Aut | 0 |  | 0 |  |  | LIN Yr7 Aut | 0.333 |  | 0.333 |  |  |  |  |  |  |  |  | [NAME] | GCSE A-G | GCSE 1-9 | Btec |  |  |  |  |  |  |  |  |  |  |  |  |  | 4 | 36.12 |  | 3 |  | D | 6 | 4 |  |  |  |  |  |  |  |  |  |  |  |  | Gg/GCSEF | Gg/GCSEF | Geography (GCSEF)
3c | 19 | 2.666 |  | 3c |  | F1 | 24 | 4.333 | 29 |  |  | 47 | 7.33 | C1 | C | 4 |  | [NAME] Yr7 Spr | 0.333 |  | 0.333 |  |  | LIN Yr7 Spr | 0.666 |  | 0.666 |  |  | SubjToProfile | ProfileType |  |  |  |  | 6a | A | 7 | D |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.1 | 38.45 |  | 3 |  | D | 6 | 4.1 |  |  |  |  |  |  |  |  |  |  |  |  | H2/GCSEF | Hi/GCSEF | Hist (2y) (GCSEF)
3L | 19 | 2.666 |  | 3c |  | F | 22 | 4 | 27 |  |  | 49 | 7.66 | B3 | B | 5.5 |  | [NAME] Yr7 Sum | 0.666 |  | 1 |  |  | LIN Yr7 [NAME] | 1 |  | 1 |  |  | Ar | [NAME] |  |  |  |  | 6b | A | 7 | D |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.2 | 40.3 |  | 4 |  | C | 7 | 4.2 |  |  |  |  |  |  |  |  |  |  |  |  | H3/BTCe | HL/BTCe | HSoc (BTCe) 2nd year
3 | 21 | 3 |  | 3b |  | F3 | 20 | 3.666 | 25 |  |  | 51 | 8 | B2 | B | 5.5 |  | [NAME] Yr8 [NAME] | 0.666 |  | 1 |  |  | LIN Yr8 Aut | 1 |  | 1.333 |  |  | Bi | [NAME] |  |  |  |  | 6c | A | 7 | D |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.3 | 42.24 |  | 4 |  | C | 7 | 4.3 |  |  |  |  |  |  |  |  |  |  |  |  | Hi/GCSEF | Hi/GCSEF | History (GCSEF)
3 | 21 | 3 |  | 3b |  | [NAME] | 18 | 3.333 | 23 |  |  | 53 | 8.33 | B1 | B | 5.5 |  | [NAME] Yr8 Spr | 1 |  | 1.333 |  |  | LIN Yr8 Spr | 1.333 |  | 1.666 |  |  | Bs | [NAME] |  |  |  |  | 5a | B | 6 | M |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.4 | 44.15 |  | 5 |  | C | 7 | 4.4 |  |  |  |  |  |  |  |  |  |  |  |  | Hi/Gen | Hi/GCSEF | History ([NAME])
3b | 21 | 3 |  | 3b |  | G | 16 | 3 | 21 |  |  | 55 | 8.66 | A3 | A | 7 |  | [NAME] Yr8 [NAME] | 1.333 |  | 1.666 |  |  | LIN Yr8 [NAME] | 1.666 |  | 2 |  |  | [NAME] | [NAME] |  |  |  |  | 5b | B | 6 | M |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.5 | 45.52 |  | 5 |  | B | 8 | 4.5 |  |  |  |  |  |  |  |  |  |  |  |  | Hl/BTCe | Hl/BTCe | Health and Social Care (BTCe)
3M | 21 | 3 |  | 3b |  | G3 | 14 | 2.666 | 19 |  |  | 57 | 9 | A2 | A | 7 |  | [NAME] Yr9 Aut | 1.333 |  | 1.666 | 10 |  | LIN Yr9 Aut | 1.666 |  | 2.333 |  |  | Bt Mu | [NAME] |  |  |  |  | 5 | B | 6 | M |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.6 | 48.2 |  | 6 |  | B | 8 | 4.6 |  |  |  |  |  |  |  |  |  |  |  |  | Ia/CNCe | It/CNCe | ICT A  (CNCe)
3a | 23 | 3.333 |  | 3a |  | W | 8 | 1.666 | 13 |  |  | 59 | 9.33 | A1 | A | 7 |  | [NAME] Yr9 Spr | 1.666 |  | 2 | 12 |  | LIN Yr9 Spr | 2 |  | 2.666 |  |  | Bt Sc | [NAME] |  |  |  |  | 5c | B | 5 | M |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.7 | 50.43 |  | 6 |  | B | 9 | 4.7 |  |  |  |  |  |  |  |  |  |  |  |  | It/CNCe | It/CNCe | ICT (CNCe)
3U | 23 | 3.333 |  | 3a |  | U |  | 0 |  |  |  | 61 | 9.66 | A*3 | A* | 8 |  | [NAME] Yr9 Sum | 2 |  | 2.333 | 14 |  | LIN Yr9 [NAME] | 2.333 |  | 3 |  |  | Bt Sr | [NAME] |  |  |  |  | 4a | C | 5 | P |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.8 | 52.48 |  | 7 |  | A | 9 | 4.8 |  |  |  |  |  |  |  |  |  |  |  |  | L2/GCSEF | Lt/GCSEF | Leisure Tourism (2nd y) (GCSEF)
4c | 25 | 3.666 |  | 4c |  | X |  |  |  |  |  | 63 | 10 | A* | A* | 8 |  | [NAME] | 2 |  | 2.666 |  |  | LIN Yr10 [NAME] | 2.666 |  | 3.333 |  |  | Cd | [NAME] |  |  |  |  | 4b | C | 4 | P |  |  |  |  |  |  |  |  |  |  |  |  |  | 4.9 | 54.77 |  | 7 |  | A | 9 | 4.9 |  |  |  |  |  |  |  |  |  |  |  |  | Lk/Gen | Lk/Gen | Life Skills (Gen)
4L | 25 | 3.666 |  | 4c |  |  |  |  |  |  |  | 65 | 10.33 | A*1 | A* | 8 |  | [NAME] Yr10 Spr | 2.333 |  | 3 |  |  | LIN Yr10 Spr | 3 |  | 3.666 |  |  | Ch | [NAME] |  |  |  |  | 4 | C | 4 | P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5 | 55.88 |  | 7 |  | A | 10 | 5 |  |  |  |  |  |  |  |  |  |  |  |  | Lt/GCSEF | Lt/GCSEF | Leisure & Tourism (GCSEF)
4b | 27 | 4 |  | 4b |  |  |  |  |  |  |  | 69 | 11 | A*1 | A* | 8 |  | [NAME] | 2.666 |  | 3.333 |  |  | LIN Yr10 [NAME] | 3.333 |  | 4 |  |  | CtLfSkl | [NAME] |  |  |  |  | 4c | C | 4 | P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5.1 | 59.07 |  | 8 |  | A* | 10 | 5.1 |  |  |  |  |  |  |  |  |  |  |  |  | Ma/GCSEF | Ma/GCSEF | Maths (GCSEF)
4 | 27 | 4 |  | 4b |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME] Yr11 Aut | 2.666 |  | 3.333 |  |  | LIN Yr11 Aut | 3.333 |  | 4.333 |  |  | CtWkSkl | [NAME] |  |  |  |  | 3a | D | 3 | L1P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5.2 | 61.28 |  |  |  |  |  | 5.2 |  |  |  |  |  |  |  |  |  |  |  |  | Me/GCSEF | Me/GCSEF | Media Studies (GCSEF)
4 | 27 | 4 |  | 4b |  | BtecGrade | GCSE Equiv | Num | Lookup Pt |  | Btecnum | PttoBtec Lookup Pt | GCSE Equiv | BtecPixlGrade | BtecGrade |  |  | Cnatnum | PttoBtec Lookup Pt | GCSE Equiv | CnatPixlGrade | CnatGrade |  | LIN Yr11 Spr | 3.666 |  | 4.666 |  |  | Dm | [NAME] |  |  |  |  | 3b | D | 3 | L1P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5.3 | 63.7 |  |  |  |  |  | 5.3 |  |  |  |  |  |  |  |  |  |  |  |  | Mu/GCSEF | Mu/GCSEF | Music (GCSEF)
4M | 27 | 4 |  | 4b |  | D* | A* | 10 | 63 |  | 0 | 0 |  |  | F |  |  | 0 | 5 |  | L1 | F |  | LIN Yr11 [NAME] | 4 |  | 5 |  |  | Dr | [NAME] |  |  |  |  | 3 | D | 3 | L1P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5.4 | 66.12 |  |  |  |  |  | 5.4 |  |  |  |  |  |  |  |  |  |  |  |  | Mu/Gen | Mu/Gen | Music (Gen)
4a | 29 | 4.333 |  | 4a |  | D1 |  | 9.333 | 59 |  | 3.666 | 25 |  | L1 | L1P |  |  | 3 | 19 |  |  | L1P |  | EXP Yr7 Aut | 0 |  | 0 |  |  | En | [NAME] |  |  |  |  | 3c | D | 3 | L1P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5.5 | 65.46 |  |  |  |  |  | 5.5 |  |  |  |  |  |  |  |  |  |  |  |  | Mx/Gen | Mx/Gen | MEX ([NAME])
4U | 29 | 4.333 |  | 4a |  | D | A | 9 | 57 |  | 6.666 | 43 |  | P3 | P |  |  | 4.333 | 28 |  |  | L1M |  | EXP Yr7 Spr | 0.333 |  | 0.333 |  |  | [NAME] | [NAME] |  |  |  |  | 2a | E | 2 | L1P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5.6 | 71.42 |  |  |  |  |  | 5.6 |  |  |  |  |  |  |  |  |  |  |  |  | P2/GCSEF | Pd/GCSEF | Prod design (2y) (GCSEF)
5c | 31 | 4.666 |  | 5c |  | D3 |  | 8.666 | 55 |  | 7 | 45 | C | P2 | P |  |  | 5.333 | 34 |  |  | L1D |  | EXP Yr7 Sum | 0.666 |  | 0.666 |  |  | Eng Lit | [NAME] |  |  |  |  | 2b | E | 2 | L1P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5.7 | 74 |  |  |  |  |  | 5.7 |  |  |  |  |  |  |  |  |  |  |  |  | Pd/GCSEF | Pd/GCSEF | DT Product Design (GCSEF)
5L | 31 | 4.666 |  | 5c |  | M1 |  | 8.333 | 53 |  | 7.333 | 47 |  | P1 | P |  |  | 6.666 | 43 |  | P3 | P |  | EXP Yr8 Aut | 0.666 |  | 1 |  |  | Fr | [NAME] |  |  |  |  | 2 | E | 2 | L1P |  |  |  |  |  |  |  |  |  |  |  |  |  | 5.8 | 76.14 |  |  | e |  |  | 5.8 |  |  |  |  |  |  |  |  |  |  |  |  | Pe/Gen | Pe/Gen | PE ([NAME])
5 | 33 | 5 |  | 5b |  | M | B | 8 | 51 |  | 7.666 | 49 |  | M3 | M |  |  | 7 | 45 | C | P2 | P |  | EXP Yr8 Spr | 1 |  | 1.333 |  |  | Ft | [NAME] |  |  |  |  | 2c | E | 2 | L1P |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Pg/GCSEF | Pg/GCSEF | PE Exam (GCSEF)
5 | 33 | 5 |  | 5b |  | M3 |  | 7.666 | 49 |  | 8 | 51 | B | M2 | M |  |  | 7.333 | 47 |  | P1 | P |  | EXP Yr8 Sum | 1.333 |  | 1.666 |  |  | Gg | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Pl/GCSEF | Pl/GCSEF | Philosophy and Ethics (GCSEF)
5b | 33 | 5 |  | 5b |  | P1 |  | 7.333 | 47 |  | 8.333 | 53 |  | M1 | M |  |  | 7.666 | 49 |  | M3 | M |  | EXP Yr9 Aut | 1.666 |  | 2 |  |  | Hi | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Level | 2 | 2 | 2 | 2 | 1 | 1 | 1 | 1 |  |  |  |  |  |  |  |  |  |  |  | Sb/BTCe2 | Sb/BTCe2 | Science BTEC (BTCe2)
5M | 33 | 5 |  | 5b |  | P | C | 7 | 45 |  | 8.666 | 55 |  | D3 | D |  |  | 8 | 51 | B | M2 | M |  | EXP Yr9 Spr | 2 |  | 2.333 |  |  | It | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Grade | A* | A | B | C | D | E | F | G |  |  |  |  |  |  |  |  |  |  |  | Sc/GCSEF | Sc/GCSEF | Science (GCSEF)
5a | 35 | 5.333 |  | 5a |  | P3 |  | 6.666 | 43 |  | 9 | 57 | A | D2 | D |  |  | 8.333 | 53 |  | M1 | M |  | EXP Yr9 Sum | 2.333 |  | 3 |  |  | Lt | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Points | 58 | 52 | 46 | 40 | 34 | 28 | 22 | 16 |  |  |  |  |  |  |  |  |  |  |  | Sl/BTCe2 | Sl/BTCe2 | Sport (BTCe2)
5U | 35 | 5.333 |  | 5a |  | W |  | 6.333 | 41 |  | 9.333 | 59 |  | D1 | D |  |  | 8.666 | 55 |  | D3 | D |  | EXP Yr10 Aut | 2.666 |  | 3.333 |  |  | Ma | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | St/GCSEF | St/GCSEF | Statistics (GCSEF)
6c | 37 | 5.666 |  | 6c |  | U |  | 0 | 5 |  | 9.66 | 61 |  | D*3 | D* |  |  | 9 | 57 | A | D2 | D |  | EXP Yr10 Spr | 3 |  | 3.666 |  |  | Me | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Ts/GCSEF | Ts/GCSEF | Science Triple (further) (GCSEF)
6L | 37 | 5.666 |  | 6c |  | X |  |  |  |  | 10 | 63 | A* | [NAME] | D* |  |  | 9.333 | 59 |  | D1 | D |  | EXP Yr10 Sum | 3.333 |  | 4 |  |  | Mu | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Tx/GCSEF | Tx/GCSEF | DT Textiles (GCSEF)
6 | 39 | 6 |  | 6b |  |  |  |  |  |  | 10.333 | 65 |  | D*1 | D* |  |  | 9.66 | 61 |  | D*3 | D* |  | EXP Yr11 Aut | 3.333 |  | 4.333 |  |  | Pe | [NAME]
6 | 39 | 6 |  | 6b |  |  |  |  |  |  |  |  |  |  |  |  |  | 10 | 63 | A* | [NAME] | D* |  | EXP Yr11 Spr | 3.666 |  | 4.666 |  |  | PhlEth | [NAME]
6b | 39 | 6 |  | 6b |  |  |  |  |  |  |  |  |  |  |  |  |  | 10.333 | 65 |  | D*1 | D* |  | EXP Yr11 Sum | 4 |  | 5 |  |  | Pd | [NAME]
6M | 39 | 6 |  | 6b |  |  |  |  |  |  |  |  |  |  |  |  |  | Exp Yr10 Aut | 2 |  | 2.666 |  |  | FRE Yr7 Aut | -1 |  | -1 |  |  | Pwl | [NAME]
6a | 41 | 6.333 |  | 6a |  |  |  |  |  |  | Points | Level | Grade |  |  |  |  | Exp Yr10 Spr | 2.333 |  | 3 |  |  | FRE Yr7 Spr | -0.666 |  | -0.666 |  |  | Sc | [NAME]
6U | 41 | 6.333 |  | 6a |  |  |  |  |  |  | 55 | E | A3 |  |  |  |  | Exp Yr10 Sum | 2.666 |  | 3.333 |  |  | FRE Yr7 Sum | -0.333 |  | -0.333 |  |  | ScAddl | [NAME]
7c | 43 | 6.666 |  | 7c |  |  |  |  |  |  | 53 | 8a | B1 |  |  |  |  | Exp Yr11 Aut | 2.666 |  | 3.333 |  |  | FRE Yr8 Aut | 0.666 |  | 1 |  |  | ScCore | [NAME]
7L | 43 | 6.666 |  | 7c |  |  |  |  |  |  | 51 | 8b | B |  |  |  |  | Exp Yr11 Spr | 3 |  | 3.666 |  |  | FRE Yr8 Spr | 1 |  | 1.333 |  |  | ScFth | [NAME]
7 | 45 | 7 |  | 7b |  |  |  |  |  |  | 49 | 8c | B3 |  |  |  |  | Exp Yr11 Sum | 3 |  | 4 |  |  | FRE Yr8 [NAME] | 1.333 |  | 1.666 |  |  | Sp | [NAME]
7 | 45 | 7 |  | 7b |  |  |  |  |  |  | 47 | 7a | C1 |  |  |  |  | Exp Yr12 Aut | 2.666 |  | 3.333 |  |  | FRE Yr9 Aut | 1.666 |  | 2 |  |  | Stats | [NAME]
7b | 45 | 7 |  | 7b |  |  |  |  |  |  | 45 | 7b | C |  |  |  |  | Exp Yr12 Spr | 3 |  | 3.666 |  |  | FRE Yr9 Spr | 2 |  | 2.333 |  |  | Te | [NAME]
7M | 45 | 7 |  | 7b |  |  |  |  |  |  | 43 | 7c | [NAME] |  |  |  |  | Exp Yr12 Sum | 3 |  | 4 |  |  | FRE Yr9 Sum | 2.333 |  | 3 |  |  | Tx | [NAME]
7a | 47 | 7.333 |  | 7a |  |  |  |  |  |  | 41 | 6a | D1 |  |  |  |  | FRe Yr7 Aut | -1 |  | -1 |  |  | FRE Yr10 Aut | 2.666 |  | 3.333
7U | 47 | 7.333 |  | 7a |  |  |  |  |  |  | 39 | 6b | D |  |  |  |  | FRe Yr7 Spr | -1 |  | -0.666 |  |  | FRE Yr10 Spr | 3 |  | 3.666
8c | 49 | 7.666 |  | 8c |  |  |  |  |  |  | 37 | 6c | D3 |  |  |  |  | FRe Yr7 Sum | -0.666 |  | -0.333 |  |  | FRE Yr10 Sum | 3.333 |  | 4
8L | 49 | 7.666 |  | 8c |  |  |  |  |  |  | 35 | 5a | E1 |  |  |  |  | FRe Yr8 Aut | 0.333 |  | 0.666 |  |  | FRE Yr11 Aut | 3.333 |  | 4.333
8 | 51 | 8 |  | 8b |  |  |  |  |  |  | 33 | 5b | E |  |  |  |  | FRe Yr8 Spr | 0.666 |  | 1 |  |  | FRE Yr11 Spr | 3.666 |  | 4.666
8 | 51 | 8 |  | 8b |  |  |  |  |  |  | 31 | 5c | E3 |  |  |  |  | FRe Yr8 Sum | 1 |  | 1.333 |  |  | FRE Yr11 Sum | 4 |  | 5
8b | 51 | 8 |  | 8b |  |  |  |  |  |  | 29 | 4a | F1 |  |  |  |  | FRe Yr9 Aut | 1.333 |  | 1.666 | 10 |  | MFL Yr7 Aut | 0 |  | 0
8M | 51 | 8 |  | 8b |  |  |  |  |  |  | 27 | 4b | F |  |  |  |  | FRe Yr9 Spr | 1.666 |  | 2 | 12 |  | MFL Yr7 Spr | 0 |  | 0
8a | 53 | 8.33 |  | 8a |  |  |  |  |  |  | 25 | 4c | F3 |  |  |  |  | FRe Yr9 Sum | 2 |  | 2.333 | 14 |  | MFL Yr7 Sum | 0 |  | 0
8U | 53 | 8.33 |  | 8a |  |  |  |  |  |  | 23 | 3a | [NAME] |  |  |  |  | FRe Yr10 Aut | 2 |  | 2.666 |  |  | MFL Yr8 Aut | 0 |  | 0.333
A | 15 | 2 |  |  |  |  |  |  |  |  | 21 | 3b | G |  |  |  |  | FRe Yr10 Spr | 2.333 |  | 3 |  |  | MFL Yr8 Spr | 0.333 |  | 0.666
B | 15 | 2 |  |  |  |  |  |  |  |  | 19 | 3c | G3 |  |  |  |  | FRe Yr10 Sum | 2.666 |  | 3.333 |  |  | MFL Yr8 [NAME] | 0.666 |  | 1
N | 15 | 2 |  |  |  |  |  |  |  |  | 17 | 2a | U |  |  |  |  | FRe [NAME] | 2.666 |  | 3.333 |  |  | MFL Yr9 Aut | 1.666 |  | 2
Q |  |  |  |  |  |  |  |  |  |  | 15 | 2b | U |  |  |  |  | FRe Yr11 Spr | 3 |  | 3.666 |  |  | MFL Yr9 Spr | 2 |  | 2.333
T |  |  |  |  |  |  |  |  |  |  | 13 | 2c | U |  |  |  |  | FRe Yr11 Sum | 3 |  | 4 |  |  | MFL Yr9 Sum | 2.333 |  | 3
W | 7 | 0.666 |  |  |  |  |  |  |  |  | 11 | 1a | U |  |  |  |  | FRe Yr12 Aut | 2.666 |  | 3.333 |  |  | MFL Yr10 Aut | 2.666 |  | 3.333
X |  |  |  |  |  |  |  |  |  |  | 9 | 1b | U |  |  |  |  | FRe Yr12 Spr | 3 |  | 3.666 |  |  | MFL Yr10 Spr | 3 |  | 3.666
 |  |  |  |  |  |  |  |  |  |  | 7 | 1c | U |  |  |  |  | FRe Yr12 Sum | 3 |  | 4 |  |  | MFL Yr10 [NAME] | 3.333 |  | 4
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr7 Aut | -0.666 |  | -0.666 |  |  | MFL Yr11 Aut | 3.333 |  | 4.333
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr7 Spr | -0.666 |  | -0.333 |  |  | MFL Yr11 Spr | 3.666 |  | 4.666
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr7 Sum | -0.333 |  | 0 |  |  | MFL Yr11 Sum | 4 |  | 5
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr8 Aut | 0.333 |  | 0.666
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr8 Spr | 0.666 |  | 1 |  | 12
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr8 [NAME] | 1 |  | 1.333 |  | 14
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr9 Aut | 1.333 |  | 1.666 | 10
Example of Excel function to calculate progress from KS2 to KS4 (or KS3) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr9 Spr | 1.666 |  | 2
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr9 Sum | 2 |  | 2.333
=IFERROR(ROUNDDOWN(VLOOKUP([KS4 Assessment],GradetoNum,3,FALSE)-VLOOKUP([KS2 Level],PttoNum,2,TRUE),2),"") |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr10 Aut | 2 |  | 2.666
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr10 Spr | 2.333 |  | 3
A Space has been entered before the = sign so the command can be pasted into the cells in the top row of a table and changed as required. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr10 [NAME] | 2.666 |  | 3.333
The basic logic is to subtract the KS2 level from the latest Assessment (or Projection or Result) via the numeric equivalents of the grades from the tables above. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr11 Aut | 2.666 |  | 3.333
The ROUNDOWN is to avoid rounding inconsistances i.e .67 .34 and have all results end in .00 or .33 or .66. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr11 Spr | 3 |  | 3.666
The IFERROR is to handle the no KS2 or KS3 or [NAME] levels/grade situation when data is missing. It simply puts "" into the result rather than #N/A . |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr11 Sum | 3 |  | 4
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr12 Aut | 2.666 |  | 3.333
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr12 Spr | 3 |  | 3.666
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MFL Yr12 Sum | 3 |  | 4