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...

 

[stct80]

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

Hi Jonomo1,

thank you for the reply. I am trying to use the Conditional formatting through Icon indicators. Where should I put this equation? I could not locate field to enter this equation for Icon stoplights, and hence the question.

Please advise..

 

[Jonmo1]

I don't think you'll be able to use icon sets for this.
They can only work on their own cells
A1 will choose the icon based on A1's value.
A1 can't can't chose the icon based on values in C1

Further, even if you chose to put them in C1, you can't use relative references in the icon set rules, only absolute hard coded values. (I don't know why)

The best you can do is the usual fill color, or borders or font etc..

 

[pjmorris]

I'm not entirely sure I follow where on the sheet you're trying to enter these formulas, but as a simple solution why not add something really small to C1, say 0.000001, ie some orders of magnitude less than your expected figures in C1 or C2. This will avoid the error condition and your formating will work. To do this simply replace '../C1' with '.../(C1+0.000001)'

HTH