HELP - Excel Formula - HELP

[princessbucki]

A colleague is working on an Excel spreadsheet for Target GCSE grades, she has formulas for most things, however, we really need to see if there is a formula for the time taking bit!

 

Each Grade in columns C through to Z are worth a certain amount of points, (A1=58; A= 52; B=46; C=40; D=34;E=28;F=22;G=16 which is the GCSE point scoring system) column AG is the total scored for all the GCSEs taken – for example Student 2 scored 372, he took 9 GCSE, but we want the score for the top 8 grades, so we subtract the lowest GCSE score which is ‘D’ worth 34 points this is shown in column AH. Total Points score subtract AF-2 should equal Total Points Score (Best 8)

 

*Could a formula be created to work out the score of the bottom GCSE grades, ie: if they took 10 GCSE’s you would want a formula to subtract the lowest 2 grade scores and insert the result in column AH.

 

*Could a formula be created to subtract the AF-2 from the Total Points Scored?

 

I’m afraid I’ve never had to work out such complicated formula’s but if someone can help that would be wonderful

 

Oh and if you don't read this until even months later and you think you can help, please reply, because it would just be wonderful to have the formula for future GCSE Gradesheets.

 

Thank you

Kirsty

KPformula.xls

[pavlovs_dog]

Have a look at Excel 2003/2004: Gradebook: Other Helpful Calculations, UWEC.

 

Any use?

[donalouise]

Are you trying to work out the capped point score? If so, I use

 

=SUM(LARGE('Range',1), LARGE('Range',2), LARGE('Range',3), LARGE('Range',4),LARGE('Range',5), LARGE('Range',6), LARGE('Range',7), LARGE('Range',8))

[donalouise]

Forgot to say that I have a seperate worksheet that references the original exam results worksheet and converts the grades in points. The formula to get the 8 highest grade then references this spreadsheet. I then hide the tab for this worksheet so that it's a bit tidier.

[princessbucki]

Forgot to say that I have a seperate worksheet that references the original exam results worksheet and converts the grades in points. The formula to get the 8 highest grade then references this spreadsheet. I then hide the tab for this worksheet so that it's a bit tidier.

 

Thank you, Im guessing this is probably what my colleague needs to do, I'm sure this is going to help no end.

[penfold_99]

If you a sims user this is possible in assesment managet then your not having to kepp exprting data.

 

You can use the MAX function and then state 1 to 8 so you can generate the capped scorce.

 

This also allows you to create a capped 8 from your current attainment

[Lee_K_81]

{=sum(large(c2:z2,{1,2,3,4,5,6,7,8}))}

 

you will to press ctrl-shift enter to get the surrounding { } 's

 

Dont forget that you now get bonus points for english and maths so it's your top 8 (which may include English and Maths) + additional bonus points for your English and Maths. so points across 10 subjects.

 

Also dont forget that any subjects worth more than 1 GCSE/equivalent needs to be split. i.e. somebody acheiving a double distinction would need to have it counted as 2 grades worth 52 and not 1 grade worth 104.

 

Lee

[Lee_K_81]

{=sum(large(c2:z2,{1,2,3,4,5,6,7,8}))}

 

you will to press ctrl-shift enter to get the surrounding { } 's

 

Dont forget that you now get bonus points for english and maths so it's your top 8 (which may include English and Maths) + additional bonus points for your English and Maths. so points across 10 subjects.

 

Also dont forget that any subjects worth more than 1 GCSE/equivalent needs to be split. i.e. somebody acheiving a double distinction would need to have it counted as 2 grades worth 52 and not 1 grade worth 104.

 

Lee

 

And a further one: if you have pupils doing early entry A-Levels i believe this can also be counted but for CVA purposes can only be counted as 1 grade dispite being a KS5 qual.