Pick Top 3 Scores

[Amean1]

I need to figure out how to automate Excel sheet to pick top 3 scores from approx 20 cells. My spreadsheet is to track approx 20 students and I would like to add formula where Excel can check the entire range (Example A1:A20) and automatically show top score to be "First", second Top Score to be "Second" and third top score to be "Third". If there are 2 students with exact same top score (i.e 25), than I want both of those entries to show as "First". I played with "Rank" function and it seem to work, but if I have same score twice than it does not appear to work. Any help you can provide would be much appreciated....

[NBVC]

Use the LARGE() function

e.g. =LARGE(A1:A20,1)
=LARGE(A1:A20,2)
=LARGE(A1:A20,3)


if you want to extract the names too, then assuming the names are in B1:B20, and the 3 LARGE formulas are in C1:C3, then:

=INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$C1,ROW($A$1:$A$20)-ROW($A$1)+1),COUNTIF($C$1:$C1,$C1)))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

[teylyn]

I read your question differently. I think you want to flag four entries if there is a tie for first.

First
First
Second
Third

If so, try this (with your scores in A2:A12)

=LOOKUP(SUM(IF(A2<$A$2:$A$12,1/COUNTIF($A$2:$A$12,$A$2:$A$12)))+1,{1,2,3,4},{"first","second","third",""})

This is an array formula and needs to be confirmed with CTRL-Shift-Enter.

cheers,

[dangelor]

Another way, using the RANK function...

=IFERROR(CHOOSE(RANK(B6,$B$6:$B$26),"First","Second","Third"),"")

[NBVC]

Yeah, I think you're right Teylyn. I think you have the right interpretation.

[Amean1]

First, I want to thank you for a quick response. I am sure that I did not apply array formula correctly as recommended by Teylyn since all the entries resulted in "first". However, Dangelar recommendation worked great and produced results as expected.

Again thank you all for your help. This is a great forum with lots of expertise and you assistance is very much appreciated!

[Amean1]

Quick observation, even though Dangelar formula provided expected results, but when I tested 2 students with same top score, it resulted in "first" for both top scores, however, second top score which should've returned "second" shows as "third". Any thoughts?

[teylyn]

Any thoughts?

Yes, use the suggestion I posted in #3. It will result in four flagged entries (first, first, second, third), not just three (first, first, third).

If you have a problem getting the formula to work, please post a sample of your data layout in an Excel file. Also remember that the formula I posted is an array formula. You enter it into the formula bar and then you need to hit CTRL-SHIFT-ENTER, not just the enter key. Otherwise, the formula will not work.

Any formula based on Rank() and/or Large() will give you only three top ranks for values with a tie. The formula I suggested will tag more than just three cells if there are tied ranks.

cheers,

[Amean1]

Thank you Teylyn for your prompt response. I executed your recommended formula and now it works great. Since I am not familiar with the array formula, I initially copied and pasted your formula right to the cell and than did the ctrl-shift-enter was the reason for it to not work. This time I pasted in the address bar and than ctrl-shift-enter and now results are exactly what I was looking for.

Again, thank you for taking the time to help. It is very much appreciated!