Highest Nth Number Number in a Range

October 14th, 2004 | Categories: Formulas | Tags:
-->

You can extract the Nth highest (or largest) number from a range by using the Large() function.

For this example we have a range A1:B5 and we would like to extract the following:

In cell B7 - The first highest number in the Range A1:B5
In cell B8 - The second highest number in Range A1:B5
In cell B9 - The third highest number in Range A1:B5

The following picture demonstrates the use of the Large() function to solve this example.
highest

Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Leave a comment | Trackback
  1. shades
    November 4th, 2005 at 16:11
    Reply | Quote | #1

    I like the LARGE and SMALL functions, but add a little more: I put the number reference in another cell, then use that cell reference to determine how many to search for. That way I can fill down as many as needed.

    In the example posted I would put 1 in cell D7, 2 in D8, then fill down.

    In C7 the formula would be

    =LARGE(A1:B5,D7)

    Then copy down as needed. This gives a little more flexibility and expandability on a couple of handy functions.

  2. Mark
    November 10th, 2005 at 17:39
    Reply | Quote | #2

    Thanks for the tip shades!