Dear co-forumers,
Congrats on the forum I have gone through many times!
Now it is my turn to post a question (I would say more of a verification needed).
So, I have to rank based on a value e.g. costs.
I have one column (Names) and one column (Costs).
Not all names have associated costs. Some are 0 (zero) so these will not be ranked.
From other threads I have found a couple of solutions for the rank to work. But I also needed another parameter: In case of a tie in costs I would like to have the ranks based on the alphabetical order of the names. I have used the below two solutions which seem to work fine I guess. I just wanted to confirm because I don't know if I am missing sth when it comes to alphabetical order vs. value order.
So my columns would look like this:
Column A: Ranks result
Column B: John, Chris, George, Peter, Mike etc...
Column C: 100, Blank, Blank, 200, 200 etc...
I have used this that seems to work: =RANK(C2,$C$2:$C$11,0)+SUMPRODUCT(--($C$2:$C$11=C2),--($B$2:$B$11<B2))
And I have also used this that seem to work: =RANK(C2,C$2:C$11)+SUMPRODUCT((C$2:C$11=C2)*(B$2:B$11<B2))
There is also this but it doesn't sort alphabetically the "tie" ones (which is ok I guess for those who don't care for the order): =RANK(C4,$C$2:$C$11,0)+COUNTIF($C$2:C4,C4)-1
I am not familiar with complex formula architecture, maybe the first two are the same thing. BTW what do the two lines (--) mean???
So my question is: Is it ok to use the first two in order to sort alphabetically in case of a tie in costs? Is there a vital difference between the two of them?
Thanks for the help and keep up the good work!
Chris
Bookmarks