Conditional Formatting
 

I am trying to format cells that meet the following criteria +/- 10,000 AND
(greater than 20% OR less than (-)20%). I can't get the rules to recognize
the negative 20%. I'm not sure if this is my issue or not. I only want one
highlighting color.

My sample is below. I'm applying the format to the 3rd column and do not
want the calculation in the 4th unless necessary. The % change referenced
above is column 3/column 1.


PM CM Change
10 20 -10 -100%
10000 0 10000 100%
12000 0 12000 100%
0 12000 -12000 #DIV/0!
20000 3000 17000 85%
20000 24000 -4000 -20%
0 #DIV/0!
2000000 2200000 -200000 -10%
2000000 2600000 -600000 -30%

Help.

Conditional Formatting
 

Andy,

Depends what you mean by "+/- 10,000". If you mean -10000 <= [3rd col] =
10000 then this will work as the conditional formatting formula:
=AND(ABS(C2)<=10000,ABS(C2/A2)=0.2)

This assumes that your example data is entered at the top left of the sheet,
so "PM" is in A1.

Cheers,
Dave

"Andy K" wrote:

I am trying to format cells that meet the following criteria +/- 10,000 AND
(greater than 20% OR less than (-)20%). I can't get the rules to recognize
the negative 20%. I'm not sure if this is my issue or not. I only want one
highlighting color.

My sample is below. I'm applying the format to the 3rd column and do not
want the calculation in the 4th unless necessary. The % change referenced
above is column 3/column 1.


PM CM Change
10 20 -10 -100%
10000 0 10000 100%
12000 0 12000 100%
0 12000 -12000 #DIV/0!
20000 3000 17000 85%
20000 24000 -4000 -20%
0 #DIV/0!
2000000 2200000 -200000 -10%
2000000 2600000 -600000 -30%

Help.

Hi, use absolute ABS function to compare -/+ values.

use this formulae in column D conditonal formatting
=AND(ABS(C2)=10000,ABS(D2)=0.2)

refer to attached sample excel sheet

all the best