Ranking formula

    Have problem in ranking, been getting lots of ties, rank formula works great off date but need to add tie breaker using birthday month and day?


    added count if but could not figure it out?


    currently using =RANK(C5,C$5:C$976,1)Sample of ranking.xlsx


    put conditional format on attachment so you can see all the ties



    thank you

    Where is the tie breaker information?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Still no birth date info in that file.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Try something like this:


    =RANK(C5,$C$5:$C$386,1)+SUMPRODUCT(($C$5:$C$386=C5)*((DATE(2004,MONTH($D$5:$D$386),DAY($D$5:$D$386)))<(DATE(2004,MONTH(D5),DAY(D5)))))

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    when I try that i changes rank to date all ending with 2000?


    any excell way to change rank numbers base on the birthday, i.e if both ar ranked at 201 the one with the elearst birthday would remain 201 and the tie would go to 202 and so on?

    Reformat the cells as General. What you describe is what that formula does.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Glad to help. :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login