79. Excel Tips - Nth Largest Value In A Range
Here's a tip on how to find the Nth largest value in a range, and use it to sort a list of numbers:
- We can use the LARGE function
- Inputs are the range of values, and N...
- Enter 1 for the largest, 2 for the 2nd... etc...
- Armed with the LARGE function, we can sort the list
Here's a screen shot of our data in Excel:
Here, we use the following formula:
=LARGE($C$8:$C$17,ROW()-ROW($F$7))
- the ROW()-ROW($F$7) piece will return N
- ROW returns the row the formula is in.
- Subtract the row of the heading, and we get N
Download sheet to practise how to Nth Largest Value In A Range in Excel
Training Video on how to Nth Largest Value In A Range in Excel:
| Attachment | Size |
|---|---|
| Nth-Largest-Value-In-A-Range.xls | 28 KB |
»
- Nick's blog
- Login or register to post comments
- 21644 reads
Large and Small Results not Accurate
Unless I have defined this wrong, The question is, What is the SECOND LARGEST or SMALLEST Value? The Example above does not test this accurately. If we have 20 values in a column with 4 of each of 5's, 4's, 3's, 2's, and 1's. And let's say this is in Column A from Rows 1 thru 20.
You would think that the following formula would give a 4 but what it gives is a 5.
=LARGE(A1:A20,2)
The LARGE command DOES NOT give you next highest value in the list. It gives you the next value in the SORTED list of values. In this Example, with four values of 5, the fourth highest will still be 5, NOT 2 as one would expect.
For the SECOND LARGEST/SMALLEST ONLY, what you REALLY want is:
For Second Largest VALUE:
=LARGE(A2:A20,COUNTIF(A2:A20,MAX(A2:A20))+1)
and Likewise the Second Smallest
=SMALL(A2:A20,COUNTIF(A2:A20,MIN(A2:A20))+1)