This question came from a data analyst in a school. A key measure of students is the value of their top 8 GCSEs. Given a spread-sheet of student results, how do you extract the best 8, given they will be different for each student. The grades (A*, A, B etc) have already been converted into scores (58, 52, 46 using a simple lookup table:
The function MAX() extracts the top score but to extract 2nd, 3rd etc there is a simple function that I love: LARGE (range, num).
LARGE(B2:N2, 2) gives the 2nd largest value in the range B2 to N2.
We can use LARGE to extract the top 8 values and then simply add them up. We want a formula that we can enter once and then copy across, so we create 8 columns labelled 1 to 8:
Cell p2: LARGE($b2:$n2,p$1)
This can then be copied across to W2 and down to W9:
This is almost there, but results in a #NUM! error where students have less than 8 results. To remove those, we use a COUNT() in column O and then an IF statement to only enter a value if the number of results for that student is greater or equal to the current column number:
These can now be added to give a total value for each student’s Top 8 score in column X.
If you have any Excel questions or hints & tips you would like shared get in touch!
If you would like any further information on the Excel courses we train, go to our website
Remember – If you’ve been on a Happy Computers course you get 2 years free helpline access. Call the helpline on 0207 375 7373.