Excel 2007 : Resolving ties in rank formula (tie in original column + tie in 2nd column)

[CRIMEDOG]

I am using the rank formula.

Please Login or Register  to view this content.

this ranks column cn (that do not have blank rows) resolving ties by looking @ column di - and works wonderfully thanks to this forum.
BUT - sometimes there is a tie in cn AND in di
I tried adding another sumproduct but that causes the rankings to skip a number
How could I resolve the double tie using column a.

To clarify: rank colmn CN / in case of tie: then break tie with column di / in case there is a tie in column CN & DI: break that tie with column A

Thank you for the help

[DonkeyOte]

It would help if we knew the content and range of each column you mention.

If for ex. we assume A, CN & DI all contain numerics and that value of each is say between 0 and 99 then you could conduct a SUMPRODUCT by weighting each value according to it's significance - eg:

CN is most import, DI next most important and A least important so

Please Login or Register  to view this content.

But as inferred the divisors will need to be revised in accordance with the range of values listed in each column - it is in essence essential that the divisors exceed max value and that each value can not overlap into the next band.

[neeharika]

What if in this case, CN is ranked in ascending order and DI and A should be in descending order. How to approach that case?

[arlu1201]

neeharika,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.