Conditional Formatting Won't Work If Cell Have Formula

[miffy45]

Hello everyone,
I'm facing a weird case while doing some conditional formatting on my report.

The condition is:
Cell turn Red if value lower equal to -0.1 and higher equal to 0.1
Cell turn Yellow if value between -0.1 and -0.05; 0.1 and 0.05

Above condition applies to Cell D4 & E4, please see my formatting below:

Red:

=(OR($D$4<=-0.1,$D$4>=0.1))

Yellow:

=(OR(AND($D$4>-0.1,$D$4<=-0.05),(AND($D$4>=0.05,$D$4<0.1))))

It works well when cell D4 with value 0.1 and cell E4 with value -0.1, both turn red.

But, when i try cell D4 with value -0.1 and cell E4 with value 0.1 turn yellow, where they should be red.

At first i tought that my formula have an issue, so i tried input the value in cell D4 and E4 manually (without the substract formula), it works perfectly.

Kindly help me point out what's wrong with my formula or format or else.
Thank you.

 

[arthurbr]

As you are dealing with small numbers you are the " victim" of floating point arithmetic
You'll have to wrap your values with the ROUND function to be on the safe side ( see the end of the article)

 

[miffy45]

ah thank you, perfect information
Hope you have a great day!

 

[arthurbr]

Most welcome