Excel: Finding the second (or Nth) largest number

Sometimes you don’t want to know the highest number in a set of numbers, but the second (or third, or fourth) highest. Excel has a built-in function to do just that for you!

Suppose you have a highscore table in Excel:

Name Highscore
Pete 235126
Rose 76243
Joe 26262
Mary 21436
Burke 7732
Thomas 3

And you wish to report the top-3 players, you can easily obtain the highest scores by using

=LARGE(B2:B7,1) (Returns the highest score)
=LARGE(B2:B7,2) (Returns the second highest score)
=LARGE(B2:B7,3) (Returns the third highest score)

To know who is doing worst in the group, just use the function =SMALL(array;k). If all you need to know is what the maximum number is, use MAX() or the minimum equivalent MIN().

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>