I am trying to figure out how to assign a rank from 1 to 100 in a column that has over 200 cells. I need an Excel formula that will keep the correct 1 to 100 rank even when the order of rows is changed. The way I manually calculate the rank is shown in the picture below:
=RANK.EQ and =RANK.AVG rank cells from 1 to however many cells are included in the reference, but I need a 1 to 100 rank.
I have tried using =IF(MAX($K:$K)=K3,100,M2-(100/COUNT($K:$K))) but because M2 is based on a cell rather than the next descending sequential number in column K, when I change the order of the rows, the rank becomes inacurrate.
