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


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!