IFERROR in nested ifs?

[jencha]

Hello All,

I'm using the following formula but want zeros when 0/0, instead getting div/0#

IF(G200<1,(G200-F200)/F200,IF(F200=0,"100%",(G200-F200)/(ABS(F200))))

How can I format this? This is for y/y growth just for context.

Jenny

 

[Jonmo1]

Just wrap the entire formula in Iferror..

Try
=IFERROR(IF(G200<1,(G200-F200)/F200,IF(F200=0,"100%",(G200-F200)/(ABS(F200)))),0)

 

[Joe4]

I am confused. You say you want 0 when 0/0, but the only way to get that is when F200 is 0.
But in your formula, you are saying that you want to return "100%" when F200 is zero.
So which is it? Do you want 0 or 100% when F200 is zero?

Whatever your choice, checking the value of F200 to see if it is zero is the first thing you want to check. If you do that, your error goes away.

 

[jencha]

Yes, what this does is returns growth rates that are x/0-1 and 0/x-1 as 100% and -100% respectively. Still pondering as to whether this is the best way to think about it...