rsides Posted March 17, 2016 Posted March 17, 2016 Hi, I am using the following formula to calculate a students progress from KS2 to KS4 (eg 3B to B1) however the students with no KS2 data or a B for below average isn't calculating correctly. =IFERROR(VLOOKUP(((VLOOKUP(LEFT(U3,1),Lookup!$A:$B,2,FALSE)-VLOOKUP(VLOOKUP(1*LEFT($O3,1),Lookup!$H:$J,2,FALSE),Lookup!$A:$B,2,FALSE))/6)*1,Lookup!$L:$M,2,FALSE),"") I am asking the formula to look at the KS4 sub level first (U3) and then the KS2 (O3). The lookups are below A:B Lookup [TABLE=width: 48] [TR] [TD]a~*[/TD] [TD=align: right]58[/TD] [/TR] [TR] [TD]a[/TD] [TD=align: right]52[/TD] [/TR] [TR] [TD]b[/TD] [TD=align: right]46[/TD] [/TR] [TR] [TD]c[/TD] [TD=align: right]40[/TD] [/TR] [TR] [TD]d[/TD] [TD=align: right]34[/TD] [/TR] [TR] [TD]e[/TD] [TD=align: right]28[/TD] [/TR] [TR] [TD]f[/TD] [TD=align: right]22[/TD] [/TR] [TR] [TD]g[/TD] [TD=align: right]16[/TD] [/TR] [TR] [TD]u[/TD] [TD=align: right]0[/TD] [/TR] [TR] [TD]x[/TD] [TD=align: right]0[/TD] [/TR] [/TABLE] H:J Lookup [TABLE=width: 101] [TR] [TD]A[/TD] [TD]F[/TD] [TD]E[/TD] [/TR] [TR] [TD]B[/TD] [TD]F[/TD] [TD]E[/TD] [/TR] [TR] [TD=align: right]1[/TD] [TD]F[/TD] [TD]E[/TD] [/TR] [TR] [TD=align: right]2[/TD] [TD]E[/TD] [TD]D[/TD] [/TR] [TR] [TD=align: right]3[/TD] [TD]D[/TD] [TD]C[/TD] [/TR] [TR] [TD=align: right]4[/TD] [TD]C[/TD] [TD]B[/TD] [/TR] [TR] [TD=align: right]5[/TD] [TD]B[/TD] [TD]A[/TD] [/TR] [TR] [TD=align: right]6[/TD] [TD]A[/TD] [TD]A*[/TD] [/TR] [/TABLE] L:M Lookup [TABLE=width: 38] [TR] [TD=align: right]-3[/TD] [TD=align: right]0[/TD] [/TR] [TR] [TD=align: right]-2[/TD] [TD=align: right]1[/TD] [/TR] [TR] [TD=align: right]-1[/TD] [TD=align: right]2[/TD] [/TR] [TR] [TD=align: right]0[/TD] [TD=align: right]3[/TD] [/TR] [TR] [TD=align: right]1[/TD] [TD=align: right]4[/TD] [/TR] [TR] [TD=align: right]2[/TD] [TD=align: right]5[/TD] [/TR] [TR] [TD=align: right]3[/TD] [TD=align: right]6[/TD] [/TR] [/TABLE] An example of it not working is a student who has a B for KS2 data and is currently achieving a D1 therefore is making 2 levels of progress however the formula is leaving the cell blank. Have I missed something from the formula to make it calculate? Thanks and I hope this is making sense.
RootsSchImp Posted March 18, 2016 Posted March 18, 2016 I think the problem is 1*LEFT($O3,1). That returns a value if the formula returns a number but if it's 1*B then it returns VALUE! ie is an error. 1
garbage46 Posted March 18, 2016 Posted March 18, 2016 (edited) I don't think your formula handles A* grades correctly. Your VLOOKUP will return 52 not 58 VLOOKUP(LEFT(U3,1),Lookup!$A:$B ,2,FALSE) only looks at the first character, therefore ignores the * As a work around you can replace VLOOKUP(LEFT(U3,1) with VLOOKUP(IF(MID(U3,2,1)="~","a*",LEFT(U3,1)) You could also use NUMBERVALUE(LEFT($O3,1)) in place of 1*LEFT($O3,1) this would allow you to remove the *1 near the end of the formula. A simpler solution would be to replace all three lookups ranges with one, shown below, and use the formula shown below. For the purpose of this example I have kept your "old" point score column and added a third for quick levels of progress calculation. I also used INDEX MATCH instead of VLOOKUP because it's just better a~* 58 10 a 52 9 b 46 8 c 40 7 d 34 6 e 28 5 f 22 4 g 16 3 u 0 0 x 0 0 =IFERROR(INDEX(Lookup!C:C,MATCH(IF(MID(U3,2,1)="~","a*",LEFT(U3,1)),Lookup!A:A,0))-IF(O3="B",2,IF(O3="N",2,LEFT(O3,1))),"") This formula will also handle a "B" or "N" at KS2. Not sure how you would handle negative progress for those with a KS4 grade of "U" or "X". You could wrap an IF statement around the formula or in an adjacent cell. if progress cell less than 0 return blank The tilde "~" in the formula can be replaced with an asterisk "*" depending on how your data is entered. EDIT: spotted another problem - in the unlikely event of a Level 3 student achieving an A* or equivalent (maybe in BTEC Performing Arts or ECDL) your original formula cannot handle 7 levels of progress. Edited March 18, 2016 by garbage46 additional info
garbage46 Posted March 18, 2016 Posted March 18, 2016 I posted here to help @rsides simplify the process of calculating levels of progress. No one has mentioned Attainment or Progress 8 - that's a different party where levels of progress and lettered sub levels no longer exist but thanks for your helpful contribution though
Banjo Posted March 22, 2016 Posted March 22, 2016 I posted here to help @rsides simplify the process of calculating levels of progress. No one has mentioned Attainment or Progress 8 - that's a different party where levels of progress and lettered sub levels no longer exist but thanks for your helpful contribution though Schools are always going to be guessing what their Progress 8 looks like. You have the variable of the students outcome (grade) and also the expected progress they should make changes each year. The DfE had proposed to move to a system in 2019 in which schools are informed three years in advance about the results each pupil will require to achieve a positive Progress 8 score. Following further analysis of the impact of qualification, curriculum and accountability reform, the DfE no longer plan to move to this system in 2019 as they cannot be certain that it will not introduce unfairness in how estimated grades are set for different prior attainment groups.
garbage46 Posted March 22, 2016 Posted March 22, 2016 Yes, totally agree! How soon do you think think schools will know their Progress 8 score following this summers results?! For the summer 2015 result was it something like 21st January 2016 when the validated KS4 Attainment 8 estimates were eventually published? I think, if rsides is still reading this thread, they should consider altering their systems to measures progress against expected progress rather than levels of progress. Expected progress being based around a concept of student's Attainment 8 estimate divided by 10 then rounded up (effectively a Progress 8 target grade). What ever that value is it is converted to the grade with the same value (for 2016). 2017 will be trickier with x.5 decimal values for unreformed grades. 1
RootsSchImp Posted March 23, 2016 Posted March 23, 2016 Good suggestion @petem46. Another thing to be aware of about 2015 P8 estimates is that P8 will change the behaviour of schools so, for example, lots of students who currently do BTEC Science will be entered for GCSEs instead. The result of this is likely to be strong Attainment 8 growth at the bottom end so reaching the 2015 expected level could translate to negative P8 scores when expectations are recalculated for 2016 and beyond.
Banjo Posted March 23, 2016 Posted March 23, 2016 Good suggestion @petem46. Another thing to be aware of about 2015 P8 estimates is that P8 will change the behaviour of schools so, for example, lots of students who currently do BTEC Science will be entered for GCSEs instead. The result of this is likely to be strong Attainment 8 growth at the bottom end so reaching the 2015 expected level could translate to negative P8 scores when expectations are recalculated for 2016 and beyond. These changes are already happening. The increase in the numbers taking English literature, and subjects that fill the three open Progress 8 slots, has come mostly from lower attaining pupils. The increase in those taking two sciences, and subjects that fill the three Progress 8 slots reserved for EBacc subjects, has come mostly from middle ability students. The increase in those taking languages and humanity subjects, meanwhile, has come predominantly from those with above-average prior attainment.
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now