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]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Your formula works for me

Fluff.xlsm
	S	T	U
1
2	00:33.456	0	5
3	00:33.456	3	7
4	00:33.456	1	6
5	00:53.234	1	8
6	00:27.275	2	4
7	00:27.275	1	3
8	00:19.567	1	1
9	00:23.458	1	2
Data

Cell Formulas
Range	Formula
U2:U9	U2	=RANK(S2,$S$2:$S$9,1)+COUNTIFS($S$2:$S$9,S2,$T$2:$T$9,"<"&T2)

 

[bc4240]

Thanks Fluff for the reply. Account updated slightly now... Excel Version 365. I wonder then if maybe a formatting choice on this end is causing the issue. My Column S cells are formatted with custom formatting mm:ss.000

I'm not sure how you entered you numbers in Column S but after considering that I went back and copy and pasted the values of S and T into a new worksheet and and tried to rank them but still no luck

 

[Fluff]

Are the values in col S calculated? If so they are all probably slightly different to each other. Change the format to Number & expand it to show the decimals.
As you can see below the numbers are different but the time is the same.

Fluff.xlsm
	R	S
1
2	00:33.456	0.000387222222222222000000
3	00:33.456	0.000387221111111111000000
4	00:33.456	0.000387222999999999000000
Data

Cell Formulas
Range	Formula
R2:R4	R2	=S2

 

[bc4240]

Ahhh true but that's the rub. 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. Then I convert the time entry to text. If I convert that result back to a number I get this:

So regardless of how the like times are derived (straight entry or time plus penalties) the times are showing the same and the ranking is the same also.

 

[Fluff]

Ok, can you post some sample data, along with the formulas.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bc4240]

OK will try ASAP

 

[bc4240]

Alas no luck with XL2BB Add-In did this:

But then got this after clicking the check box to add the add-in

 

[Fluff]

Have a look here XL2BB Icons greyed out

 

[bc4240]

The problem seems to be in the rank function itself on my end. If I have some same times show up in a list and just try RANK or RANK.EQ functions I get one value ranking higher than its twins. The
COUNTIFS portion will not work unless all three 00:33.456 below are ranked 7