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?