Ranking Riddle

[bc4240]

So here's the set up. I need to rank a time in Column S in ascending order (fastest to slowest), but with consideration of the number of penalties in Column T. It seems like it should be easy, but it's a bit vexing.

The ranking is no problem as long as everyone has a slightly different time.

But if by chance two opponents had the same time....one as a result of no penalty and the other as a result of a penalty....I want the team with the most penalties to be ranked lower.

In the above example you can see how the original Ranking attempt (yellow) is displayed using this formula in U2 and copied down to U9 - RANK(S2,$S$2:$S$9,1)+COUNTIFS($S$2:$S$9,S2,$T$2:$T$9,"<"&T2)

Ranking values are the same for the 00:.28.275 times although one of those times has 2 penalties and the other has only 1. The same happens for the 00:33.456 times although one has 0 penalties...one has 1 and the other has 3 penalties.

It should be:

00:33.456 - 0 - 5
00:33.456 - 3 - 7
00:33.456 - 1 - 6

If I re-rank Column U again in Cloumn V2:V9 using the same formula but referencing the Rank1 column ratrher than the Time Column - RANK(U2,$U$2:$U$9,1)+COUNTIFS($U$2:$U$9,U2,$T$2:$T$9,"<"&T2)

I get closer but still it's not assigning the highest rank to the highest penalty holder with the same scores.

Any ideas??

 

[Fluff]

That once again suggests that those values are not all exactly the same.

 

[bc4240]

You are certainly correct.... I saw this thread

RANK gives different rank for same value

Hi guys, I'm getting totally crazy over the RANK-function. In this sheet, B2 = RANK(A2,$A$2:$A$3) and B3 = RANK(A3,$A$2:$A$3), and as you can see, it gives me two different ranks for the same value. These are constants (not formulas) and prove that the value is the same, formula in C2 =...

www.mrexcel.com

Which suggest that the way I have my values presented is causing the RANK Function to misbehave. If I copy and past the time values or in my case use the userform to create the times with functions and then try to rank them I get the a misfire on the RANK Function. But if I just type in the values like this they rank perfectly.

Any ideas for work around to this problem??

 

[Fluff]

If you leave the times as numbers, rather than converting to text, does it rank correctly?

 

[bc4240]

YES!!! SOLUTION!!!

If you remember I said this above:

My number values are the same. I am using a user form to enter the time and the number of penalties. I enter the time as straight numbers 23456 (00:23.456) and then from the cell where that straight number is entered I convert the value using =TIME(0,0,E2)*10+TEXT(P2,"00\:00\.000") where TIME(0,0,E2) is the number of penalties times 10 seconds each.

but if I use this formula in those cells instead. It ranks correctly.

NUMBERVALUE(TIME(0,0,E2)*10+TEXT(P2,"00\:00\.000"))

Thanks Fluff for helping me think about this differently.

 

[Fluff]

Glad it's sorted & thanks for the feedback.