Break Ranking Ties with multiple Criteria

Hi Mysore,

You are getting Final Rank in col U after adding P and S, but here S has all the zeros.
Please elaborate little more on this as to how you want to obtain Final Rank. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Mysore,

col P and col R has the data against Rank 15 only... surprisingly.
If I understood correctly, you need to rank the results in col O considering the results in col P and col R. Please confirm.

Also, would like to suggest you to look at the Excel inbuilt help for Rank function which has something to break the ties :-

Extract from the help:-
"
For some purposes one might want to use a definition of rank that takes ties into account. In the previous example, one would want a revised rank of 5.5 for the number 10. This can be done by adding the following correction factor to the value returned by RANK. This correction factor is appropriate both for the case where rank is computed in descending order (order = 0 or omitted) or ascending order (order = nonzero value).

Correction factor for tied ranks=[COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1)]/2.

In the following example, RANK(A2,A1:A5,1) equals 3. The correction factor is (5 + 1 – 2 – 3)/2 = 0.5 and the revised rank that takes ties into account is 3 + 0.5 = 3.5. If number occurs only once in ref, the correction factor will be 0, since RANK would not have to be adjusted for a tie.
"

Regards,
DILIPandey

<click on below 'star' if this helps>

can you look at this one. thanks.


Copy of Break Ranking Ties.xlsx

ok. don't know why it does show..
but here on my unit the first 15 - 379.73 is ranked 15
and the second one 15 - 330.09 is ranked 16

is this correct

here's the formula.
=IF(($O35=""),"",RANK(O35,O$8:O$82,1)+ SUMPRODUCT((O$8:O$82=O35)*(Q$8:Q$82>Q35))

when i try to integrate column R then its again a tie. don't know why...

Originally Posted by Mysore

Guys,

Still looking for a solution. Any help from anybody?

Thanks

Hi
problem what you want with this Q & R has equal weight will end up #VALUE!.

Reason column O cell both say 15. Then colomn Q cell 1 got 379.73 and other 330.09. Then column R cell 11% and other 12%. Then column S cell both 0. This will become #VALUE! reason 379.73 with 11% and other 330.09 with 12% is equal make #VALUE! on all.

What I can't work out. Why 379.79 is 11% and other 330.09 is 12%. Really 379.79 should be more about 14%. This will work in Rank. if you decide no this is what you want. Then there nothing more can to do to help unless do what Vlady did without column R cell. Reason 379.79 will rank 15 and 309.09 will Rank 16. That how it work.

Good luck