Ranking Riddle

bc4240

Board Regular
Joined
Aug 4, 2009
Messages
134
Office Version
  1. 365
Platform
  1. Windows
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.

Untitled-1.jpg


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??
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
STU
1
200:33.45605
300:33.45637
400:33.45616
500:53.23418
600:27.27524
700:27.27513
800:19.56711
900:23.45812
Data
Cell Formulas
RangeFormula
U2:U9U2=RANK(S2,$S$2:$S$9,1)+COUNTIFS($S$2:$S$9,S2,$T$2:$T$9,"<"&T2)
 
Upvote 0
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
 
Upvote 0
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
RS
1
200:33.4560.000387222222222222000000
300:33.4560.000387221111111111000000
400:33.4560.000387222999999999000000
Data
Cell Formulas
RangeFormula
R2:R4R2=S2
 
Upvote 0
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:

Screenshot 2023-10-31 062938.png


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.
 
Upvote 0
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.
 
Upvote 0
Alas no luck with XL2BB Add-In did this:

Screenshot 2023-11-01 095720.png


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

Whoops.png

Whoops2.png
 
Upvote 0
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


Rank Wrong.png
 
Upvote 0

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top