Quote:
Originally Posted by Andy K
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