Average Points Score and Best 8 Capped

[park_bench]

Hello,

Does anyone know how the DofE works out average point score and best 8 (capped) point score for pupils?

This is the explanation document from the DofE website (TECHNICAL ANNEX) but the figures I get using this method don't make sense and apparently the document is years out of date (point scores for GCSE grades are old - i.e. A*=4; A=3.5; B=3...)

Does anyone know how its done?

Thanks in advance,

Ben

[park_bench]

OK - the above is a live link on the DofE website to an out of date document.

The present method of calculation can be found here (Value added technical information)

Ben

[tombry]

Are you trying to do this for yourself? I was asked to do this based off of my school's targets/estimated grades, so I exported the year 11 marksheet (which held the data) to Excel - I gave point values to A*, A, B, etc. (using find/replace - for A* that's "A~*") I then made the targeted columns a named range, and the estimated columns a named range. Inserted a column at the end of the sheet, and put in a formula that was like:

=AVERAGE(LARGE(Estimated 2:2, {1,2,3,4,5,6,7,8})) - replacing "Estimated" with "Target" for the target column, and changing the amounts for students that aren't taking 8, after running it, I used the Ready Reckoners sheet that was passed to me via an email and checked our estimated VA against last years actual VA, and they were pretty close, so I feel like this is what was requested of me (may not be what you are trying to do though)

[baggiesboy]

I am trying to do the same calculation, without having to put 200-odd students individually into Raiseonline's Ready Reckoner. The method described here: education.gov.uk/schools/performance/secondary_11/KS2-4_General_VA_Guide_2011_FINAL_AMENDED.pdf - pages 15-16 - seems considerably more complicated than the method described in the link posted by tombry above. I'm a bit confused about which method to use now!
Thanks
John

[tombry]

I haven't messed with the individual student one yet - only the whole school one.

[park_bench]

Are you trying to do this for yourself? I was asked to do this based off of my school's targets/estimated grades, so I exported the year 11 marksheet (which held the data) to Excel - I gave point values to A*, A, B, etc. (using find/replace - for A* that's "A~*") I then made the targeted columns a named range, and the estimated columns a named range. Inserted a column at the end of the sheet, and put in a formula that was like:

=AVERAGE(LARGE(Estimated 2:2, {1,2,3,4,5,6,7,8})) - replacing "Estimated" with "Target" for the target column, and changing the amounts for students that aren't taking 8, after running it, I used the Ready Reckoners sheet that was passed to me via an email and checked our estimated VA against last years actual VA, and they were pretty close, so I feel like this is what was requested of me (may not be what you are trying to do though)

Hi I am trying to do this for myself but it is complex. The method you describe is good but it doesn't take into account the fact that different courses have different GCSE equivalences (Depending on how your data is set up I guess) and that short courses are only worth 0.5 of a GCSE although they could be one of the top results.

It is tricky... I'm working on it though.

[skunk]

Challenge accepted!

Lawks, it looks like a tough one though.

[tombry]

Hi I am trying to do this for myself but it is complex. The method you describe is good but it doesn't take into account the fact that different courses have different GCSE equivalences (Depending on how your data is set up I guess) and that short courses are only worth 0.5 of a GCSE although they could be one of the top results.

It is tricky... I'm working on it though.

Yeah I was asked to do it and told the weights didn't matter for what I was doing - I think it was a reduced list of students though.

I'll have to work on the proper one eventually, but I have a whole ton of stuff to focus on first.

[s1monb]

Hey

I was tasked with doing this earlier this in the year and set up an Excel sheet to do this for me. If you want a copy PM me and I'll email it to you.

[tmcd35]

=AVERAGE(LARGE(Estimated 2:2, {1,2,3,4,5,6,7,8}))

This looks like what I'm looking for, but I have an additional complication I wonder if an Excel expert could help me out on.

I am using VLOOKUP to bring the point scores for each GCSE subject into 1 table. Now no pupil does every GCSE subject, so for the subject they are not taking excel puts a '#N/A' in that column. The above formula is not taking this in to account and always returns '#N/A'.

Anyone got any ideas. Maybe I can alter the VLOOKUP to return '0' instead of #N/A if the pupil can't be found in the referenced subject table?

[tmcd35]

Scrap that! Answered my own quetion:

<code>
=IFERROR (VLOOKUP(a1,target!a1:z99,4,FALSE),0)
</code>

[skunk]

I tend to use the following:

<code>
=if(a1="","",VLOOKUP(a1,target!a1:z99,4,FALSE))
</code>

as this shows any errors that are not related to having a blank cell a1.