# 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

• Sample of ranking.xlsx

the problem I am having is breaking ties using the birthday month and day, ranking working good but have tie that i can't seem to fix?

• 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

we only use month and day to brake tiesSample of ranking.xlsx

• 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

• that did it! thank you so much for you time and help!