• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List Toppers

P

prasaddn

Active Member
Hi Folks,


I have small challenge in my day to day work. I am sure team here can easily solve it for me. Well, I have list of Candidate Names in ColA and their Scores in Col B.


I need to list top 3 scorer's Name in Col C and their scores in Col D. I think it is done by CSE formulaes!


I tried using Large(Array, k) in three cells, and offset function to get names, it works fine, but fails when you have more than one person with same top score either at 1st, 2nd or 3rd.


I do not want to use VBA coding, only Formulas please.


pls suggest.


Regards,

Prasad
 
Chandoo has some excellent tutorials on dashboards on the main page that include how to sort and identify the X largest/smallest. This also covers handling the non-unique values. You should give them a look.


http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/


Briefly, you need two more columns. The first is a key number column (1-x) for all your names. The second column you take your scores and add the key number/1E6 (or larger divisor). So a score of 10 for the first person is now 10.000001, but cell formatting can maintain the original display. Every non-unique score is now unique!
 
My 2 cents here:


1. Can't you just sort column B?

2. You can use conditional formatting to identify the top 3 scores. I have tried it myself. And if there are two identical scores on the 3rd rank, it will hi-light all 4 scores. And if there are two identical top scores, then it'll hi-light for instance, 98, 98, and 89.


there is no need to add additional columns.
 
If you can't sort, and truly only care about the first 3 entries, and don't care about ties (ie, if 4 people all score 100, only the first 3 people get listed), this formula should work:

=INDEX(A:A,SMALL(IF($B$2:$B$10>=LARGE($B$2:$B$10,3),ROW($B$2:$B$10)),ROW(A1)))


If you want to show all entries (include ties) use this one:

=IF(ROWS(C$2:C2)>COUNTIF($B:$B,">="&LARGE($B:$B,3)),"",INDEX(A:A,SMALL(IF($B$2:$B$10>=LARGE($B$2:$B$10,3),ROW($B$2:$B$10)),ROW(A1))))


Both formulas can be placed into C2 and then copied down and across. If using latter formula, copy down about 6 cells or so. * Both formulas are arrays, need to be confirmed using Ctrl+Shift+Enter *
 
Thank you Team. :)


I am using Luke M first formula and dragging to some 10 rows to be sure I dont miss any ties.


Regards,

Prasad DN
 
hi Luke,


i make up a list of 40 scores and I deliberately give the higher scores toward the bottom of the list and the second formula cannot capture the truly high score. Why is that? Did I do something wrong?
 
Hi fred,

Do all the range callouts go far enough, ie, all the B2:B10 ranges changed to B2:B50?

I'd be curious as to what the formula is displaying...errors, or just not the right data?
 
Luke, what's the email address I should send to? I think a spreadsheet would be easier to understand what I may have missed from your formula. thanks.
 
Here it is.


https://docs.google.com/spreadsheet/ccc?key=0AjjeX0fo1xi7dG5JVjhuZXA4aTZKdjctX1dSLWszTVE&hl=en_US


I realized Google doc has removed my conditional formatting...hmm. Anyway, thanks for the help.
 
Looking at cells C2:C4, the formula uses a range of B2:B10. C5:C40 use range B2:B40. They all need to callout B2:B40
 
The only problem I have left is that


C2 showing up the name AA which is supposed to be ranked 2nd.

C3 showing up the name AG which is supposed to be ranked 1st. and

C4 showing up the name AM which is supposed to share 2nd rank.
 
Yep, you caught me. I was taking the OP literally in just "identifying" the top scorers, not putting them in order. <grin>

Only way I know of doing that is using another helper column, like Mike suggested (or do it my way, and then just resort.
 
While I definitely thank all you for helping me in the situation above. I found another way of getting it. I used pivot table and choose top 10 option. I know I mentioned "only formulaes", because I did not want to use vba coding or sorting options. Once again thank you all.
 
You must log in or register to reply here.
Back
Top