Nov 16 2008

# Find the 2nd Largest Value in a Range

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(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

Did you find this post helpful? Try signing up for our free daily tips.