+ Reply to Thread
Results 1 to 2 of 2

Ordinal Ranking or places, allowing ties after soring scores

  1. #1
    Registered User
    Join Date
    09-06-2012
    Location
    Ohio
    MS-Off Ver
    Dunno
    Posts
    1

    Ordinal Ranking or places, allowing ties after soring scores

    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!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Ordinal Ranking or places, allowing ties after soring scores

    Is this solved?

    If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1