Excel formula for Capped Point Score/Best 8 per pupil

[JamesDM]

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?

[Pico]

I think your formula could be rewritten as:

 

=SUMPRODUCT(L2:BF2*--((L2:BF2>=LARGE(L2:BF2,8))))

 

Then you could simply add in the weighting as follows:

 

=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.

[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?

[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?

Edited November 23, 2012 by Pico
Corrected myself.