## How to find the Nth Largest in Excel

Both MIN and MAX in Excel will return the Minimum or Maximum value from a list.  But what if you want to return a value that is the Nth largest in Excel? Or even the Nth smallest . How would you go about this type of ranking?

You could use one of Excels Rank functions and sort by rank, but this is not really the best option.

### OFFSET to find the Nth Largest in Excel

You could also use the OFFSET function.  To do this you would first need to sort your data.  If you are looking for the Nth Largest, the sorting would be from the highest value to the lowest value. =OFFSET(A1,D2,0)

Where:

• A1 is the starting position.  I have selected my column header as the starting position.
• D2 is where I have entered the nth largest number I wish to find.  In this example it is 5

What the OFFSET function will do is move by the number of rows and columns you tell it to move and then return the value from that cell.

By selecting D2, which contains the value of 5 in this case, we are telling offset to move down 5 rows from the starting point, which is the column header.

At the end of the function we see 0.  This represented the number of columns we want to move.  By the way, the value returned using the offset function is 143, the 5th largest number in the list.