Excel Tip > Excel Functions List > Excel Text Formulas > Returning the Nth Largest / Smallest Values in a Range in Microsoft Excel 2010

Returning the Nth Largest / Smallest Values in a Range in Microsoft Excel 2010

Fb-Button

If we have a list and we want to find the nth largest or smallest number then we can use the LARGE and SMALL functions.

 

LARGE function:

Syntax: =LARGE(array,k)

 

Example:

We have a list of numbers in A1:A9. We need to get the 2nd largest number from this list.

1. Go to Cell D1
2. Type the LARGE formula
3. =LARGE(A1:A9,2)
4. Press Enter on your keyboard
5. From the list 81 is the 2nd largest number
6. We get the result as 81 in cell D1.

 

Nth1
SMALL function:

Syntax: =SMALL(array,k)

 

Example:

We have a list of numbers in A1:A9. We need to get the 2nd largest number from this list.

1. Go to Cell D3
2. Type the SMALL formula
3. =SMALL(A1:A9,2)
4. Press Enter on your keyboard
5. From the list 23 is the 2nd smallest number
6. We get the result as 23 in cell D3

 

IMAGE2

Previous
Next

Comments

  1. Sathish

    If you want the second highest number you can use

    =LARGE(A1:A9,2)

    although that doesn't account for duplicates so you could get the same result as the Max

    If you want the largest number that is smaller than the maximum number you can use this version

    =LARGE(A1:A9,COUNTIF(A1:A9,MAX(A1:A9))+1)

    Reply ↓
  2. Sathish

    If you want the largest number that is smaller than the maximum number you can use this version

    =LARGE(A1:A9,COUNTIF(A1:A9,MAX(A1:A9))+1)

    Reply ↓
  6. Carlos

    How would you eliminate the values if there are duplicates? For example, in your sample list, if the value 88 is repeated and you use the same formula, it will return 88 instead of 81. I want the 2nd highest value regardless of any duplicate values.

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Excel Tips

Categories

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.