How to find the Nth Largest in Excel

in #excel5 years ago

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.

nth Largest in Excel

=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.

If you want to learn more about the OFFSET function have a read of OFFSET Function to make Excels SUM function Dynamic.

Alternative to OFFSET - SMALL and LARGE


The drawback of using OFFSET is the requirement to have your data sorted. Therefore it can be prone to human error. Excel has a nice little function, well two functions really that will allow you handle this. So check out the video below for this time saving nifty Excel trick.

And Hay, don't forget to give this video the thumbs up!


Sign up for my newsletter. Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox



SIGN UP NOW

In return for this Tip - nth Largest in Excel - I ask that you share this post or the video with your friends and colleagues

Sort:  

Congratulations @theexcelclub! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You received more than 2000 as payout for your posts. Your next target is to reach a total payout of 3000

You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Vote for @Steemitboard as a witness to get one more award and increased upvotes!