Conditional format stoplight for #Div/0! error

[stct80]

Happy Autumn!

I am trying to flag a cell, pointing to 2 different cells, if the following condition is met:

1. For cell A1: If(C1-C2)/C1>.15 --> Red stoplight.
2. For cell A1: If(C1-C2)/C1>.05 --> Yellow stoplight.
3. For cell A1: If(C1-C2)/C1<=0.05 --> Green stoplight.

This is working fine with the Conditional format.

Issue:

If Cell C1 = 0 (i.e. Denominator is 0), I get a standard error #Div/0! (which is technically correct per the excel logic).


I would like to show same set of indicators, even if the Denominator = 0. I know this may contradict the logic, but the objective is to flag if the C2 value is lower than C1, for dashboard reporting.

Can I get some help?

Thank you and Regards...

 

[mole999]

you could
=iferror(If(C1-C2)/C1>.15),"") and just loose the issue

 

[stct80]

you could
=iferror(If(C1-C2)/C1>.15),"") and just loose the issue

Thank you mole999, for a quick response.

May be because of my lack of expertise in excel formulas, but will this retain my original formula with the respective stop light logic, even if the issue is observed for a particular cell?

Please advise..

 

[mole999]

the intention is for your div error to not appear, so no highlight should appear, give it a try on one thats already erroring

 

[Jonmo1]

you could
=iferror(If(C1-C2)/C1>.15),"") and just loose the issue

The formula is being used in conditional formatting, not in a standard cell formula.

@stct80
What exactly should happen when C1=0

 

[stct80]

the intention is for your div error to not appear, so no highlight should appear, give it a try on one thats already erroring

Dear mole999,

Sorry, I am still confused. I am assuming you want me to put this formula in the cell (and not in the Conditional Formula equation).

If so, I already have a preexisting formula in the respective cell to calculate the cell value and display the Excel Stoplights.

Example: =(C1-B2)/C1. If this value is >.15, I get a 'Red Stoplight' indicator.

 

[mole999]

my error, i was thinking it was cell triggered, what i suggested isn't going to help there

 

[Jonmo1]

What exactly do you want to happen when C1 = 0 ?

 

[stct80]

What exactly do you want to happen when C1 = 0 ?

No problem! Actually, it was tricky for me to put on the email, without the actual data and hence the confusion. Sorry.

If C1=0, I am still trying to rely on the % metric to show - when C2 is different w.r.t. C1, and hence display the stoplight colours with the following logic, but driven by the Numerator calculated value. I know, it is difficult to digest this need (if there is a zero value in denominator):

For cell A1: If(C1-C2)/C1>.15 --> Red stoplight.
2. For cell A1: If(C1-C2)/C1>.05 --> Yellow stoplight.
3. For cell A1: If(C1-C2)/C1<=0.05 --> Green stoplight.

 

[Jonmo1]

So you're doing

IF C2 is more than 15% less than C1 = Red
If C2 is more than 5% less than C1 = Yellow
If C2 is less than 5% less than C1 = Green

Try

=IF(C1=0,C2,(C1-C2)/C1) > 0.15 - Red
=IF(C1=0,C2,(C1-C2)/C1) > 0.05 - Yellow
=IF(C1=0,C2,(C1-C2)/C1) < = 0.15 - Green