Excel Hints & Tips: The LARGE Function

Excel Training LondonHow do you list the top 8 values in each row?


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:

Pic 1

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:

Pic 2

Cell p2: LARGE($b2:$n2,p$1)

This can then be copied across to W2 and down to W9:

Pic 3

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:


Pic 4

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.

Happy Computers

1 response to “Excel Hints & Tips: The LARGE Function”

Leave a Reply

Your email address will not be published. Required fields are marked *