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 spreadsheet 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:
A simple lookup table
The function MAX() extracts the top score but to find the next highest, there is a simple function that I love: LARGE (range, num).
LARGE(B2:N2, 2) gives the second largest value in the range B2 to N2.
We can use LARGE to extract the top eight values and then simply add them up. We want a formula that we can enter once and then copy across, so we create eight columns labelled 1 to 8:
Eight 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 eight 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:
=IF($O2>=P$1,LARGE($B2:$N2,P$1),””)
These can now be added to give a total value for each student’s Top 8 score in column X.
How could the LARGE function save you time at work?
Related Blogs
- Calculations on a Filtered List in Excel – In this blog, learn how to create a dropdown filter in your Excel spreadsheets.
- Save Time in Excel with Autofill – Learn how to use the useful tool Autofill. Nicky explains how in the two minute video.
- How Microsoft Excel Can Increase Your Productivity – Billy talks about some of the reasons why Microsoft Excel is so indispensable to productive workplaces in this blog.
