Find the 2nd Largest Value in a Range
- 0 Comments
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:
[ad#in-post-ad]
=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.