Conditoinal formatting

[Chad]

Hi

I was wondering if someone could help with this conditional formatting
problem. Here is the formula which I would like to split into three
conditional formatting criteria. It works well in cell but does not
work when I split it into a condition. Is there a reason for this or
am I doing something wrong.


=IF((B2/B1)-1>-5%,"Red",IF((AND((B2/B1)-1>-10%,(B2/
B1)-1<=-5%)),"Orange",IF((B2/B1)-1<=-10%,"Green",0)))

Any help or pointing in the right direction would be of great
assistance.

Chad

 

[Chad]

OK I have solved it. Here is how it is done for anyone who may
search this topic in futute.

Condition 1 Red

=IF(B2>=B1*0.95,TRUE,FALSE)

Condition 2 Yellow

=IF(B2>=B1*0.9,TRUE,FALSE)

Condition 3 Green

=IF(AND(ISNUMBER(B2),B2>0),TRUE,FALSE)


Take it easy

Chad

 

[IanC]

I'm offline at the moment so by the time I reconnect someone may already
have posted something similar

You can shorten the conditional formulae as follows:

Red
=B2>=B1*0.95
Yellow
=B2>=B1*0.9
Green
=AND(ISNUMBER(B2),B2>0)

This is because conditional formatting is looking for a satisfied condition
(ie a "TRUE" state) so there's no need to give an "If" condition in this
case.