How to find the Nth largest or smallest values in a range in Excel 2007 ?

Here’s a interesting tip on using the Excel function to find the Nth largest value and the smallest value in Excel 2007 .

1. Use the LARGE function and specify the right parameters to find the largest value .

Eg : If you want to find the 2nd largest value in a list of 8 numbers from the cell A1 to A6 , use the following formula
=LARGE(A1:A6,2)

The funtion LARGE takes the following parameters

Parameter 1 is the range / list of values and the second parameter (in the example ) is the 2nd largest value in the list .

2. Similarly , one can use the SMALL function to get the Nth SMALLEST value in the list in Excel

=SMALL(A1:A6,2) will result in the 2ns smallest value