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