Trying to use RANK function with multiple parameters

[AshleyWee]

Hi there

I'm looking to create a formula that ranks my data based on two parameters and where one of these parameters overrides another. I will write out some background information as I feel like it will help put things into context.

In the sport of show jumping (with horses) I need to rank horses based on penalties and time. For the purpose of this, penalties over rides time. example, a horse with 4 penalties and a time of 78.256 seconds, will not rank as high as a horse with 0 penalties and a time of 80.256.

I have columns that give total penalties and time but how do I best rank there using a formula as I need. The number of horses in a set of data can vary.

Thanks for your help!!!

[oeldere]

You get better help if you add an excel file, without confidential information.

Please also add the desired (excpected) result.

[protonLeah]

To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[ChemistB]

Look at maximum time expected, let's say it's 3 digits (i.e. never more than 999 seconds).
Now with your penalties in column B and your time in column C
In a blank column (let's say F)
=IF(ISNUMBER(C2), B2*1000+C2,"")
Then
Rank based on F
Does that help?

[daddylonglegs]

Hello AshleyWee, welcome to Excel Forum

Lets assume you have Penalties in B2:B10 and Times in C2:C10 then try this formula in D2 copied down

=RANK(B2,B$2:B$10,1)+COUNTIFS(B$2:B$10,B2,C$2:C$10,"<"&C2)

That will rank on penalties, lowest ranking highest, and effectively use the time as a tie-break between horses with the same number of penalties.

[AshleyWee]

Optimum Time Worksheet.xlsx

Thank you for everyone's input so far. I have not been able to get it working, yet I feel like it is close. I have attached the worksheet with the data.

I am trying to get a formula in column AA (Position) which shows the ranks ranks each sequence first by the TOTAL NUMBER OF PENALTIES (column Y) and then by the TIME DIFFERENCE (column Z). I am happy with the how the rest of the sheet is working, just stuck on this part...

Thank you again everyone

[oeldere]

I think you have to unmerge all merged cells to get this working.

Maybe another forummember has an alternative.

[daddylonglegs]

You should be able to use a version of the formula I suggested, i.e. in AA9

=IFERROR(RANK(Y9,Y:Y,1)+COUNTIFS(Y:Y,Y9,Z:Z,"<"&Z9),"")

copy the formula down the column

[AshleyWee]

Thank you very much daddylonglegs. It works perfectly.

All the best