# Excel Hints & Tips: The LARGE Function

In this blog, Jonny explains how to use the LARGE Function to extract the top values in a row and even add them up.

## Hi, we are Happy

We are leading a movement to create happy, empowered and productive workplaces.

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? 