Find the 2nd Largest Value in a Range
- 4 Comment
Excel provides an easy way to list the k-th (first, second, third, etc.) largest number in a range. We’ll also take a look at the opposite situation where you want to find the 2nd smallest value in a range, for example.
Large & Small Formulas
So let’s go ahead and take a look at the formulas:
=Large(range, k)
where range is the list of number you want to find the values of, and k is the largest number you want to find. Similarly, the Small formula looks like this:
=Small(range, k)
where range is again the list of numbers you want to find the values up, and k is the smallest number you want to find. Now’s let’s look at a few examples.
Examples
Large and Small Formula Example
=LARGE(B2:B6,2) will return 86, since 86 is the 2nd largest number in that range of numbers.
=LARGE(B2:B6,5) will return 12, since it is the 5th largest number in the range (also the smallest in this example)
=SMALL(B2:B6,1) will return 12, since it is the smallest number in the range.
=SMALL(B2:B6,3) will return 45 for the 3rd largest number.
** Excel Hints provides Excel Tips and Excel Help for All Levels.
More Excel Tips
4 Comments on this post
Trackbacks
-
Kevin said:
AHT (Sec) AHT Ranking Amt of Day At Work
640 65 5
699 85 4
852 117 5
774 100 5
737 94 5How do i return a value in the first colum as the lowest number if the matching row value is 5 in the third column?
Thanks,
KevinJanuary 23rd, 2009 at 1:43 pm -
john said:
This is a tricky formula. Use the small formula in a similar manner to this:
=SMALL((B1:B10=5)*A1:A10,ROWS(B1:B10)-COUNTIF(B1:B10,5)+1)
where Column B contains the 4’s and 5’s and column A is the column of data you are trying to get the min from. After you enter this formula, you will have to hit Ctrl+Shirt+Enter to get it to work. Brackets {} will show up surrounding the formula in the formula bar if you have done it correctly.
Hope this helps,
JohnJanuary 23rd, 2009 at 3:07 pm -
Joe said:
Hi,
I have a similar problem,
How can I find the largest number in a range of numbers in a single col, and delete that number from the cell leaving the rest of the numbers unchanged?
I will need to put this in a macro.I’m having trouble using MATCH, LARGE, ADDRESS.
Thanks for the help
JOeJune 2nd, 2009 at 11:13 am -
john said:
Hi Joe. This doesn’t sound like it would be too difficult to handle with a macro. Would you want to shift up the deleted number or just clear the cell of that value?
June 3rd, 2009 at 8:46 pm
Loading ...