Imagine you are asked to figure out the top 5 scorers with their scores from class A. Two or more students can have the same score.
Following the values in 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% |
To Find the Top 5 Values :
LARGE returns the kth largest value in a data set whereas the ROW returns the row number.
=LARGE($E$7:$E$16,ROW(A1)) <=> =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.
To Find the Top 5 Scorers :
There are two ways to compute this :
1. Non-array formula ( Press Enter to confirm it)
=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 it)
Explanation:
1. INDEX- MATCH looks for a value in the right column of a table and returns a value in the same row from a column you specify.
2. MATCH always the first occurence so we first need to create unique values in range
ROW($E$7:$E$16)/10^5 - This part is used to create unique values in range E7:E16.
It evaluates as follows :
7/100000
8/100000
9/100000
.
.
.
16/100000
3. $E$7:$E$16-ROW($E$7:$E$16)/10^5
And substract the above fraction values from each values of range E7:E16.
0.97-(7/100000)
0.69-(8/100000)
.
.
0.6-(16/100000)
It gives us unique values.
About Author:
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 has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.
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