Excel formula to numerically rank preference order

New Contributor

I am trying to create an excel formula that will numerical rank stages in a cycle based on distance from the present stage. So, for example, there are 4 Stages. We are in Stage 3. I would like the formula to assign the highest numeric rank to Stage 3 (=4), one rank lower to Stage 4 (=3), Stage 1 would be ranked 3rd (=2), and Stage 2 would be ranked the lowest (=1) because it is furthest in cyclical terms from the present Stage 3. If I change the present stage to 1, the ranking needs to recalculate the order.


I suspect there is an easy formula to resolve this but I just don't have the brainpower. Any one can help?

2 Replies


See the attached sample workbook. I used several defined names; you can inspect them in Formulas > Name Manager.

Many thanks, Hans. Accomplishes exactly what I was looking to accomplish. Also taught me some new functions to use in Excel. Many thanks for your response and effort!