I couldn't find a solution for this problem so I made one and thought I'd share it.
[The first part, the easy part]
I had scores for many people, made a list that showed the highest ranked person first, next column would pull the highest number. Then I had the 2nd highest and on down through the rest.
I did this part with =INDEX($A$1:$A$20, MATCH(LARGE($C$1:$C$20,1), $C$1:$C$20,0))
and something like =INDEX($B$3:$B$19, MATCH(LARGE($C$1:$C$20,1), $C$1:$C$20,0))
So, basically, this is referencing a table that spans A1 to E20.
Things to know:
That first cluster (Where I'm refrencing Column A or B) that is the info. it returns.
That "1" you see? Change it to a 2 and you'll get the 2nd biggest number/corresponding row's info. 3, 4... etc.*
*(Instead of using the number, you could put a reference here a dummy column that just has 1,2,3,4... then it will be easier
to move the formula down the page and it will take less exact 'clicking' : ]
Now, when you have a tie, this thing doesn't work. A solution for this that I stole from online : ) is to have a column that adds an insignificant decimal number to each score, the decimal is based on the Excel row number so you're guaranteed to have no ties since no row numbers are identical.
Had my scores in Column B, in Column C, I used:
=B1+ROW(A1)/1000000 ...=B1+ROW(C1)/1000000 would work just as well, I think.
Next column down would be: =B2+ROW(A2)/1000000
[Second part where I'm not explaining as much!]
So there I was... I had a sorted list based on my table, top scorers to bottom scorers with their scores next the names. (Tied scores will come up in order of who's name appears first on the table)
BUT I wanted to display ranks next to each name (1st, 2nd, 3rd... etc.)
So, I made this formula... Say Column "E" has my scores, well, I know that the score on top is going to be the 1st place score, so in the same row, I have:
=IF(E1=0,"","1st")
..get's trickier as you go on because you want to be able to handle having multiple tie scores, this is what works. (By the way, the system I'm using, which is more difficult, has it so that if, say, 2 people tied for 1st, the third place guy still gets third, because two people beat you. Sports don't really work like this, right? But I'm not doing sports.)
=IF(E1=0,"","1st")
=IF(E2=0,"",IF(E2=E1,"1st","2nd"))
=IF(E3=0,"",IF(E3=E1,"1st",IF(E3<E2,"3rd","2nd")))
=IF(E4=0,"",IF(E4=E1,"1st",IF(E4=E2,"2nd",IF(E4<E3,"4th",IF(LARGE(E1:E4,3),"3rd",IF(LARGE(E1:E4,2),"2nd","?"))))))
=IF(E5=0,"",IF(E5=E1,"1st",IF(E5=E2,"2nd",IF(E5=E3,"3rd",IF(E5<E4,"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))
=IF(E6=0,"",IF(E6=E1,"1st",IF(E6=E2,"2nd",IF(E6=E3,"3rd",IF(E6=E4,"4th",IF(E6<E5,"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))
=IF(E7=0,"",IF(E7=E1,"1st",IF(E7=E2,"2nd",IF(E7=E3,"3rd",IF(E7=E4,"4th",IF(E7=E5,"5th",IF(E7<E6,"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))
=IF(E8=0,"",IF(E8=E1,"1st",IF(E8=E2,"2nd",IF(E8=E3,"3rd",IF(E8=E4,"4th",IF(E8=E5,"5th",IF(E8=E6,"6th",IF(E8<E7,"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))
=IF(E9=0,"",IF(E9=E1,"1st",IF(E9=E2,"2nd",IF(E9=E3,"3rd",IF(E9=E4,"4th",IF(E9=E5,"5th",IF(E9=E6,"6th",IF(E9=E7,"7th",IF(E9<E8,"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))
=IF(E10=0,"",IF(E10=E1,"1st",IF(E10=E2,"2nd",IF(E10=E3,"3rd",IF(E10=E4,"4th",IF(E10=E5,"5th",IF(E10=E6,"6th",IF(E10=E7,"7th",IF(E10=E8,"8th",IF(E10<E9,"10th",IF(LARGE(E1:E17,9),"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))))
=IF(E11=0,"",IF(E11=E1,"1st",IF(E11=E2,"2nd",IF(E11=E3,"3rd",IF(E11=E4,"4th",IF(E11=E5,"5th",IF(E11=E6,"6th",IF(E11=E7,"7th",IF(E11=E8,"8th",IF(E11=E9,"9th",IF(E11<E10,"11th",IF(LARGE(E1:E17,10),"10th",IF(LARGE(E1:E17,9),"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))))))
=IF(E12=0,"",IF(E12=E1,"1st",IF(E12=E2,"2nd",IF(E12=E3,"3rd",IF(E12=E4,"4th",IF(E12=E5,"5th",IF(E12=E6,"6th",IF(E12=E7,"7th",IF(E12=E8,"8th",IF(E12=E9,"9th",IF(E12=E10,"10th",IF(E12<E11,"12th",IF(LARGE(E1:E17,11),"11th",IF(LARGE(E1:E17,10),"10th",IF(LARGE(E1:E17,9),"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))))))))
=IF(E13=0,"",IF(E13=E1,"1st",IF(E13=E2,"2nd",IF(E13=E3,"3rd",IF(E13=E4,"4th",IF(E13=E5,"5th",IF(E13=E6,"6th",IF(E13=E7,"7th",IF(E13=E8,"8th",IF(E13=E9,"9th",IF(E13=E10,"10th",IF(E13=E11,"11th",IF(E13<E12,"13th",IF(LARGE(E1:E17,12),"12th",IF(LARGE(E1:E17,11),"11th",IF(LARGE(E1:E17,10),"10th",IF(LARGE(E1:E17,9),"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))))))))))
=IF(E14=0,"",IF(E14=E1,"1st",IF(E14=E2,"2nd",IF(E14=E3,"3rd",IF(E14=E4,"4th",IF(E14=E5,"5th",IF(E14=E6,"6th",IF(E14=E7,"7th",IF(E14=E8,"8th",IF(E14=E9,"9th",IF(E14=E10,"10th",IF(E14=E11,"11th",IF(E14=E12,"12th",IF(E14<E13,"14th",IF(LARGE(E1:E17,13),"13th",IF(LARGE(E1:E17,12),"12th",IF(LARGE(E1:E17,11),"11th",IF(LARGE(E1:E17,10),"10th",IF(LARGE(E1:E17,9),"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))))))))))))
=IF(E15=0,"",IF(E15=E1,"1st",IF(E15=E2,"2nd",IF(E15=E3,"3rd",IF(E15=E4,"4th",IF(E15=E5,"5th",IF(E15=E6,"6th",IF(E15=E7,"7th",IF(E15=E8,"8th",IF(E15=E9,"9th",IF(E15=E10,"10th",IF(E15=E11,"11th",IF(E15=E12,"12th",IF(E15=E13,"13th",IF(E15<E14,"15th",IF(LARGE(E1:E17,14),"14th",IF(LARGE(E1:E17,13),"13th",IF(LARGE(E1:E17,12),"12th",IF(LARGE(E1:E17,11),"11th",IF(LARGE(E1:E17,10),"10th",IF(LARGE(E1:E17,9),"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))))))))))))))
=IF(E16=0,"",IF(E16=E1,"1st",IF(E16=E2,"2nd",IF(E16=E3,"3rd",IF(E16=E4,"4th",IF(E16=E5,"5th",IF(E16=E6,"6th",IF(E16=E7,"7th",IF(E16=E8,"8th",IF(E16=E9,"9th",IF(E16=E10,"10th",IF(E16=E11,"11th",IF(E16=E12,"12th",IF(E16=E13,"13th",IF(E16=E14,"14th",IF(E16<E15,"16th",IF(LARGE(E1:E17,15),"15th",IF(LARGE(E1:E17,14),"14th",IF(LARGE(E1:E17,13),"13th",IF(LARGE(E1:E17,12),"12th",IF(LARGE(E1:E17,11),"11th",IF(LARGE(E1:E17,10),"10th",IF(LARGE(E1:E17,9),"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))))))))))))))))
=IF(E17=0,"",IF(E17=E1,"1st",IF(E17=E2,"2nd",IF(E17=E3,"3rd",IF(E17=E4,"4th",IF(E17=E5,"5th",IF(E17=E6,"6th",IF(E17=E7,"7th",IF(E17=E8,"8th",IF(E17=E9,"9th",IF(E17=E10,"10th",IF(E17=E11,"11th",IF(E17=E12,"12th",IF(E17=E13,"13th",IF(E17=E14,"14th",IF(E17=E15,"15th",IF(E17<E16,"17th",IF(LARGE(E1:E17,16),"16th",IF(LARGE(E1:E17,15),"15th",IF(LARGE(E1:E17,14),"14th",IF(LARGE(E1:E17,13),"13th",IF(LARGE(E1:E17,12),"12th",IF(LARGE(E1:E17,11),"11th",IF(LARGE(E1:E17,10),"10th",IF(LARGE(E1:E17,9),"9th",IF(LARGE(E1:E17,8),"8th",IF(LARGE(E1:E17,7),"7th",IF(LARGE(E1:E17,6),"6th",IF(LARGE(E1:E17,5),"5th",IF(LARGE(E1:E17,4),"4th",IF(LARGE(E1:E17,3),"3rd",IF(LARGE(E1:E17,2),"2nd","?"))))))))))))))))))))))))))))))))
That's it - this may come in handy for someone, I couldn't find a similar solution on the web.. you could expand on this pattern of course. Good luck!
Bookmarks