I am hosting an event with 25 participants. Each participant will receive a score based on how many times they complete the given task in the allowed time. There will be checkpoints along the way which will be used as tie-breakers (i.e. completing the 30th task faster than another participant completes it will result in winning the potential tie-breaker). Whoever completes the most tasks in the given time will be ranked 1. Second most will be ranked 2. Ties are not allowed. I am working with 4 columns. Column A is populated with participants names. Column B is populated with each participant's numerical score (larger is

better). Column C is each participant's time taken to complete the last checkpoint (smaller isbetter. Column D is rank based on Column B.If 2 or more participants have the same numerical score in Column B, then Column D (rank) should be decided by Column C, instead of Column B for those participants.

I played with this a bit and it seems like Pearson's section related to "Double Ranking And Tie Breaking" should work with one minor change. At Chip Pearson's site, the tie breaking is based on using the highest value in both the main score and the tie breaking score to find the winner. In the OP's situation, the main score is based on the highest value, but the tie breaker is based on the lowest value.

It appears that the formula for the "composite score" needs to be changed so that the second term is subtracted from the first term instead of added:

{=(C23/MAX(ABS($C$23:$C$32)))

-(E23/(10^(MAX(LEN(C$23:C$32)+1))))}

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll