Break a tie using the Rank function

4.70K viewsFormulas and Functions

I have a model that scores, based on several criterea, and then ranks varous capital investments. The problem I have is that I have end up with several tie resulting ffrom projects with the same score. What I would like to do is break this tie based on one of the various critera such as IRR. So if a tie comes up it will be broken by the project with the larger IRR. Does anyone have a good method for breaking ties using the Rank function?


I’m sure other people have better methods, but the way I would do this is to (i) rank the tiebreak, (ii) combine the two rankings into a new “well-behaved” score, and then (iii) rank the new score.

One way of combining two ranks of n items is to imagine a new number written in base n notation, with the most important rank being the first digit. In other words, ranks a and b map to a*n + b if there are n items. Comparisons of this new score should then do the right thing. A model is attached which shows how this works.

BTW, are you sure you want to use IRR rather than NPV? :)

You are viewing 1 out of 1 answers, click here to view all answers.