I think that I see what’s going on.
On P123, ranks are always against the universe, and you’re changing the universe. I realize that it may appear to be a trivial change in this case, but it’s not.
I made a spreadsheet and attached it to this post to illustrate this.
Column A started out as three character sequential tickers. (ABC, DEF,…XZA…XYZ) The spreadsheet was resorted, so now it’s just gibberish.
The top of Columns B through F are weights, as if you had weighted your ranking criteria. Rows 3 through 28 of Columns B through F are just random numbers, 1 to 99.
Column H is the sum of weightings times “raw data” and Column I then percentile ranks Column H. The spreadsheet is sorted by Column I. Imagine that Column I is the “All Stocks” rank. (I’m assuming that the random number 1 to 99 is good enough as a rank, by the way. We actually deal with ties a little more elegantly, but I ignored it in this case.)
Column K is a coin flip. If it’s 1, it will include the company in the rankings that follow. If it’s a zero, it won’t.
Column V sums the survivor’s weighted ranks and then Column W ranks those raw sums.
You’ll notice that column W is in a different order than column I. You can recalculate all you want and you’ll see the rankings change each time. Remember, the only moving part in this spreadsheet is column K, and all that it’s doing is running a 50% change to toss each row.
This is why the ranks change: It’s not just the end result that’s being resorted, but all of the individual rank criteria.
Rank Example.xls (29.5 KB)