In this article we will cover how to lookup the top N values in a dataset. If there are multiple entries with the same value, only one instance of that value will be considered in determining the top N values.
Let's take a scenario where you are tasked with identifying the top 5 scorers and their respective scores in class A. It is possible for two or more students to have the same score.
See the table below showing the values which are in cells D6:E16. (Download this data file)| Names | Scores |
| James | 97% |
| Dave | 69% |
| Sam | 97% |
| Rohan | 79% |
| Aamir | 45% |
| Ranbir | 55% |
| Hrithik | 74% |
| Shahrukh | 74% |
| Bean | 37% |
| John | 60% |
=LARGE($E$7:$E$16,ROW(A1))LARGE returns the kth largest value in a data set whereas the ROW returns the row number.
=LARGE($E$7:$E$16,ROW(A1)) can be read as =LARGE(RANGE,1)
Nested LARGE with ROW function to automatically increments the rank position.When you paste the formula down it automatically finds 1st,2nd,3rd,4th,5th largest values.How to find the Top Scorers in Excel
There are two ways to compute the top 5 scores in MS Excel :1. Non-array formula
=INDEX($D$7:$D$16,MATCH(LARGE(INDEX($E$7:$E$16+(ROWS($E$7:$E$16)-ROW($E$7:$E$16))/10^5,0),ROWS($1:1)),INDEX($E$7:$E$16+(ROWS($E$7:$E$16)-ROW($E$7:$E$16))/10^5,0),0))
2. Array formula
Press Ctrl Shift Enter to confirm the formula below (instead of just ENTER).
=INDEX($D$7:$D$16,MATCH(LARGE($E$7:$E$16-ROW($E$7:$E$16)/10^5,ROWS($1:1)),$E$7:$E$16-ROW($E$7:$E$16)/10^5,0))Explanation:
- INDEX-MATCH is a combination of functions in Excel that allows you to find a value in one column of a table and retrieve a corresponding value from another column in the same row.
- The MATCH function is used to find the position of the first occurrence of a value in a range. To ensure uniqueness in the range, we divide the row numbers by 10^5. For example, ROW($E$7:$E$16)/10^5 evaluates to 7/100000, 8/100000, 9/100000, and so on.
- To create unique values in the range $E$7:$E$16, we subtract the fraction values obtained in the previous step from each value in the range. For example, $E$7:$E$16-ROW($E$7:$E$16)/10^5 results in 0.97-(7/100000), 0.69-(8/100000), 0.6-(16/100000), and so on.
- The subtraction operation generates unique values in the range, allowing you to perform operations or comparisons based on those unique values.
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Thanks for sharing this excellent formula.
ReplyDeleteNice article. In my experience, ROWS($1:1) won't work in an array formula. It will generate all 1's, unlike with the non-array version. I usually use ROW(INDIRECT("1:5")) to generate that sort of list within an array formula.
ReplyDeleteHi Thanks for the file. i dont want to ignore duplicate, lets say if we have following data: 10, 10, 10, 9, 9,8 then it should generate 10,9,8
ReplyDeleteWhat's the use of ROWS($1:1)
ReplyDelete