# Excel formula for Capped Point Score/Best 8 per pupil

Printable View

• 22nd November 2012, 01:16 PM
JamesDM
Excel formula for Capped Point Score/Best 8 per pupil
Has anyone got a solution for calculating capped point score per pupil in Excel? So far I've got

SUM(LARGE(L2:BF2,{1,2,3,4,5,6,7,8}))

but this doesn't take into account the GCSE weighting for equivalencies e.g. if a BTEC subject is in the eighth highest add this twice.

I already have a row with GCSE weighting per subject on it so I almost need to combine the above formula with SUMPRODUCT...somehow?
• 23rd November 2012, 12:06 PM
Pico
I think your formula could be rewritten as:

Code:

`=SUMPRODUCT(L2:BF2*--((L2:BF2>=LARGE(L2:BF2,8))))`
Then you could simply add in the weighting as follows:

Code:

`=SUMPRODUCT(L2:BF2*\$BG2:\$DA2*--((L2:BF2>=LARGE(L2:BF2,8))))`
where \$BG2:\$DA2 contain your weightings for each subject column (except this might need to be an absolute range reference depending upon how you've set up your spreadsheet).

HTH.
• 23rd November 2012, 01:14 PM
JamesDM
Thanks for the reply but I can't get this to work, using the formula above gives me a value error. I tried using , instead of * to seperate the arrays in the formula but this just gives a total SUMPRODUCT, not a largest 8 SumProduct?
• 23rd November 2012, 02:22 PM
Pico
Make sure that all your arrays are the same size. That's the most likely source of the value error.

Any chance you could paste the formula that was generating the error or a mock up of what your spreadsheet looks like?