How to Use the LARGE Function in Excel

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:

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:

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.