Conditional Formatting for a range situation

[JayF]

Attempting to write a conditional format as follows:
1) Green if value in cell is +/- or = 2.5% {example 97.5% to 102.5% = Green}
2) Yellow if value in cell is +/- >2.5% and < = 5.0% {example 97.4% to 95.0%
and 102.6 to 105.0 = Yellow)
3) Red if value in cell > +/- 5% {example 94.9% and less and 105.1% and
higher = red.

I am using Conditional Formatting with condition #1 = Formual is
'AND(+b4>0.9749,+b4<1.0251)'. Question. How do I write the next two
conditions? Is there a logic stmt for less then and equal? Greater then or
equal. I am new at this. Hope there is a template for this type of
Conditional formatting.

[Duke Carey]

Since the conditional formatting is applied in the order of the conditions,
you can simply use the second range in the 2d condition, like so

=AND(+b4>0.949,+b4<1.051)

and the last range in the 3rd condition



"JayF" wrote:

> Attempting to write a conditional format as follows:
> 1) Green if value in cell is +/- or = 2.5% {example 97.5% to 102.5% = Green}
> 2) Yellow if value in cell is +/- >2.5% and < = 5.0% {example 97.4% to 95.0%
> and 102.6 to 105.0 = Yellow)
> 3) Red if value in cell > +/- 5% {example 94.9% and less and 105.1% and
> higher = red.
>
> I am using Conditional Formatting with condition #1 = Formual is
> 'AND(+b4>0.9749,+b4<1.0251)'. Question. How do I write the next two
> conditions? Is there a logic stmt for less then and equal? Greater then or
> equal. I am new at this. Hope there is a template for this type of
> Conditional formatting.

[daddylonglegs]

Here's one way

condition 1 red
=(B4<>"")*(ABS(B4-1)>0.05)

condition 2 yellow
=(B4<>"")*(ABS(B4-1)>0.025)

condition 3 green
=(B4<>"")*(ABS(B4-1)>0)

[JayF]

Excellent. Simple and direct...I like it

"daddylonglegs" wrote:

>
> Here's one way
>
> condition 1 red
> =(B4<>"")*(ABS(B4-1)>0.05)
>
> condition 2 yellow
> =(B4<>"")*(ABS(B4-1)>0.025)
>
> condition 3 green
> =(B4<>"")*(ABS(B4-1)>0)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=527766
>
>