Conditional formatting based on a positive or negative variance.

Morning All.
I currently have a column that shows the variance of actual volume to forecasted volume. The numbers change color based on the %. It is currently set up to shade green when less than or equal to 5%, yellow if between 5 and 10 % then red if over 10%. However, it also needs to shade if those are negative as well. So if it's between -5% and 5% it would be green if its between -5% to -10% and 5% to 10% it would be yellow and then anything over 10% and under -10% would need to be red. I have tried different variations and am unable to get it to work properly.

try wrapping the value in ABS() that will turn -5 into 5
then I'm no so sure what are the formulas you're currently using?

Use Formula is option and then reference the cell in question within and ABS() function

e.g. =ABS(A1)<5%

Hi,

How about these, presuming actual and forecast are in columns A and B.


=ABS((A1-B1)/A1)<=0.05
=AND(ABS((A1-B1)/A1)>0.05,ABS((A1-B1)/A1)<=0.1)
=ABS((A1-B1)/A1)>0.1