Average Points Score and Best 8 Capped

• 15th February 2012, 12:13 PM
park_bench
Average Points Score and Best 8 Capped
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?

Ben
• 15th February 2012, 12:47 PM
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
• 26th February 2012, 02:44 PM
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)
• 27th February 2012, 02:14 PM
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
• 27th February 2012, 09:17 PM
tombry
I haven't messed with the individual student one yet - only the whole school one.
• 2nd March 2012, 11:44 AM
park_bench
Quote:

Originally Posted by 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)

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.
• 2nd March 2012, 12:04 PM
skunk
Challenge accepted!

Lawks, it looks like a tough one though.
• 4th March 2012, 09:25 AM
tombry
Quote:

Originally Posted by park_bench
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.
• 28th March 2012, 10:31 AM
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.
• 4th December 2012, 08:21 AM
tmcd35
Quote:

Originally Posted by tombry

=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?
• 4th December 2012, 08:26 AM
tmcd35
Scrap that! Answered my own quetion:

<code>
=IFERROR (VLOOKUP(a1,target!a1:z99,4,FALSE),0)
</code>
• 5th December 2012, 02:30 PM
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.