Conditional formatting with odd behaviour

[Armsp0]

I require conditional formatting to apply a cell color, either red or green depending on whether it is greater than a critical value contained in an adjacent cell or less than or equal to that same value. The cell to be formatted displays the result of a calculation whose precedents are themselves adjacent in the same row. If the precedents are some values less than 1.000 the formatting works fine. If the precedents are greater than 1.000, the equal to component of the formatting fails to apply the required cell fill color when the calculation result equals the critical value. I have fiddled with this for a while trying the simplest version of the formatting in a stepwise manner, used Excels pre defined rules instead of my formatting formula but to no avail.

The liked file shows two rows illustrating the problem. in both rows the calculated result is equal to the critical value but the formatting is different in each cell.


https://www.dropbox.com/s/5u7xcvo9bzd7kks/condform.xlsm?dl=0

 

[etaf]

For some reason excel is actually working out the RED calc as
0.008000000000000010000000000000

and NOT
0.008000000000000000000000000000

I'm not sure why its doing that - the way excel handles number precision or a bug

use round
ROUND(ABS(D7-C7),5)
and then the number of decimals places you are likely to use
I have used 5 here

 

[Armsp0]

Wow, it did occur to me that there might be an issue with precision but I only checked to a few more places. (and arithmetically shouldn’t have required more precision anyway so far as I could see) Thanks for your thorough investigation Wayne.