Conditional Formatting

P

pltplt

How do I write the formulas to conditionally format my cells as follows?
Less than or equal to + or - 2% = green
Between + or - 2.01% and + or - 3.99% - Yellow
Greater than + or - 4% = red
 
T

Tom Hutchins

In the Conditional Formatting dialog, select "Formula Is". In the adjacent
textbox, enter =ABS(A1)>=0.04

Click the Format... button. On the Patterns tab, click the Red color, then OK.
In the Conditional Formatting dialog, select Add >>. Select "Formula Is",
and in the adjacent textbox, enter =AND(ABS(A1)>0.02,ABS(A1)<0.04)

Click the Format... button. On the Patterns tab, click the Yellow color,
then OK.
In the Conditional Formatting dialog, select Add >>. Select "Formula Is",
and in the adjacent textbox, enter =ABS(A1)<=0.02

Click the Format... button. On the Patterns tab, click the Green color, then
OK. Finally, click OK to close the Conditional Formatting dialog. Use the
Format Painter to apply this conditional formatting to other cells as desired.

Hope this helps,

Hutch
 
P

pltplt

Thank you, I knew I had to use ABS but I just couldn't get it right. This is
the last step I needed to complete my Performance Improvement Scorecard.
Since I set it up for the year I need the cells to stay unformatted when
empty. With your help my formula now looks like this. and it works.
IF(AND(ABS(A1)<=0.02,A1<>"")
 
Top