Rank on Two Criteria

[Kieranz]

Hi All

We are doing a fun raising “Bicycle Race”! We would like to have a leaderboard to display bikers timetaken. So for purpose of example I have biker name Column; Stage Completed Column and TimeTaken column.

Data is as follows:

Aaa 4 5:00

Bbb 3 4.40

Ccc 1 1:30

Ddd 1 1:25

Eee 2 3:10

Fff 3 4:30

Ggg 4 5:10

Leaderboard should look like:

1 Aaa

2 Ggg

3 Fff

4 Bbb

5 Eee

6 Ddd

7 Ccc

Of course the leaderboard will continue to change as each Stage is completed and the winner is one with least time taken.

How do I rank Stage Completed and Time Taken to reflect the leaderboard.

Many thks!

KNZ

 

[Hui]

Kieranz


Firstly, Welcome to the Chandoo.org forums


I assume you want the highest Stage Completed but the lowest Time Taken


I would add a helper column where I would have a formula like

=(Total Stages-Stage Completed) x 100 + Time Taken


If you have more than 100 bikers

=(Total Stages-Stage Completed) x 1000 + Time Taken


Then rank on this new helper Column

The lowest number will be first etc

 

[NARAYANK991]

Hi Kieranz ,


There are two methods you can use , one of which just sorts the data in the order of Stages Completed and Time Taken ; sorting on the Stages Completed column is in the Largest to Smallest order , while sorting on the Time Taken column is in the Smallest to Largest order.


If you wish to use formulae , using a helper column makes it straightforward :


If your Stages Completed data is in column B , and Time Taken data is in column C , then put in the following formula in column D , starting with say D2 :


=B2-C2%%


Now , in column E , starting with cell E2 , put in the formula :


=INDEX(Bikers_Names,MATCH(LARGE($D$2:$D$8,ROW(A1)),$D$2:$D$8,0))


Copy both formulae downwards as far as your data extends.


Narayan

 

[Kieranz]

Hui and Narayan

Most grateful. Thank you

KN