How to Get the Nth Largest or Smallest Value in Excel

Sometimes, you may want to find the Nth largest or smallest value in a range of cells in Excel. For example, you may want to find the second highest score, the third lowest price, or the fifth most frequent item. In this article, we will show you how to use Excel formulas to get the Nth largest or smallest value in a range of cells. We will also explain the basic theory behind these formulas and provide a detailed example with real data.

To get the Nth largest or smallest value in a range of cells, we can use the LARGE or SMALL function in Excel. These functions take two arguments: an array and a number (N). The LARGE function returns the Nth largest value in the array, while the SMALL function returns the Nth smallest value in the array.

For example, suppose we have a range of cells A1:A10 that contain some numbers. To get the second largest value in this range, we can use the formula =LARGE(A1:A10,2). To get the third smallest value in this range, we can use the formula =SMALL(A1:A10,3).

Procedures

To use the LARGE or SMALL function in Excel, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type =LARGE( or =SMALL( depending on whether you want the Nth largest or smallest value.
3. Select the range of cells that contain the values you want to analyze, or type the range address. For example, A1:A10.
4. Type a comma (,) to separate the arguments.
5. Type the number (N) that represents the position of the value you want to get. For example, 2 for the second largest or smallest value, 3 for the third largest or smallest value, and so on.
6. Type a closing parenthesis ()) to complete the formula.
7. Press Enter to get the result.

Example

Let’s see an example of how to use the LARGE and SMALL functions in Excel. Suppose we have a table that shows the sales of 10 products in a month, as shown below:

We want to find the following values:

• The highest sales amount
• The second highest sales amount
• The lowest sales amount
• The second lowest sales amount

To get these values, we can use the following formulas:

• The highest sales amount: =LARGE(B2:B11,1)
• The second highest sales amount: =LARGE(B2:B11,2)
• The lowest sales amount: =SMALL(B2:B11,1)
• The second lowest sales amount: =SMALL(B2:B11,2)

The results are shown below:

As you can see, the formulas return the correct values for the Nth largest or smallest sales amount in the range B2:B11.

Other Approaches

Besides using the LARGE and SMALL functions, there are other ways to get the Nth largest or smallest value in Excel. Here are some of them:

• Using the SORT function: The SORT function can sort a range of cells by one or more columns in ascending or descending order. You can use the SORT function to sort the values in the range and then use the INDEX function to return the value at the Nth position. For example, to get the second highest sales amount, you can use the formula =INDEX(SORT(B2:B11,-1),2). The SORT function sorts the values in B2:B11 in descending order, and the INDEX function returns the value at the second row.
• Using the RANK function: The RANK function can return the rank of a value in a range of cells. You can use the RANK function to find the rank of each value in the range and then use the IF function to return the value that matches the Nth rank. For example, to get the second highest sales amount, you can use the formula =IF(RANK(B2,B2:B11,0)=2,B2,""). The RANK function returns the rank of the value in B2 in the range B2:B11 in descending order, and the IF function returns the value if the rank is 2, or an empty string otherwise. You can copy this formula to the other cells in the range to get the other values.