Rank formula to include cells with error.

[bcom93]

Trying to rank data based on a % to last years sales. The formula I am using here is =IFERROR((IF(AND(F4=0,E4>0),E4/100,IF(AND(F4<0,E4>0),SUM(E4,ABS(F4))/100,(E4-F4)/F4))),"-"). Certain instances have 0 is both columns resulting in the "-".
EX here :

Sales (column E)	LY Sales (column F)	% to LY (column G)
0	410	-100%
0	0	-

I am then taking the above data and attempting to rank however it will not rank the cell that comes back with "-". Noting here that no matter what I have changed it to I cannot get my ranking formula to rank this. It keeps returning a #value! error. Using this formula =RANK(G4,$G$4:$G$15,0)+COUNTIF($G$4:G4,G4)-1

Let me know if more data is needed to support.

 

[myall_blues]

What is your desired outcome for the rank?
The simplest solution is to change the IFERROR value from a string (currently "-") to a number, such as 0 or -999.
However that might not poduce a desirable outcome. Changing it to zero will rank the zero as 1, -100% as 2, while changing it to -999 will reverse those rankings.

Alternatively you could let it error and use this formula to calculate the rank instead.

=IF(ISERR(G4),"",COUNTIF($G$4:$G$15,">"&G4)+1)

 

[bcom93]

What is your desired outcome for the rank?
The simplest solution is to change the IFERROR value from a string (currently "-") to a number, such as 0 or -999.
However that might not poduce a desirable outcome. Changing it to zero will rank the zero as 1, -100% as 2, while changing it to -999 will reverse those rankings.

Alternatively you could let it error and use this formula to calculate the rank instead.

=IF(ISERR(G4),"",COUNTIF($G$4:$G$15,">"&G4)+1)

View attachment 110730

Hi there,

I have tried to use -100% as that ranks it however it does not rank it last like I would prefer. I have other instances where there are 0 sales this year, but sales last year so it is ranking those last which I guess isn't the end of the world, thanks for your help!