Conditional Formating

[Beneindias]

Hey guys,

I´m having a strange problem with excel and hope that someone can help me.

So, I have an excel file with a table that compares prices.

In collumn S, I have the comparison between price that I paid and my selling price.

And I have conditional formating to change cell color in this row, depending on my margin.

So, I have 3 rules, value=0, color green, value>0, orange and values<0, colors red.
(I have the rules in this order)

The problem is, I have cells with value=0 that are green, others are orange and even red...

The cells with values >0 and <0 seem to be workin fine.

I've already changed order of rules, ticked off and on the "Stop if true" box, but it's always the same result.

Thanks to all who can help

 

[Joe4]

Are the cells you are formatting calculations?
If so, you may have the famous "floating arithmetic" issue going on, as described here: Floating-point arithmetic may give inaccurate result in Excel - Office
I would recommend updating your calculations to use the ROUND function to round the numbers off to get rid of any tiny fractional differences.

 

[SanjayGMusafir]

ROUND function to round

I was typing the same while @Joe4 replied.

 

[Beneindias]

And that was the cause.

I've never heard of that before.
Thanks for the solution.

 

[Joe4]

And that was the cause.

I've never heard of that before.
Thanks for the solution.

You are welcome.
Yeah, it can be an annoying little quirk that rears its ugly head when you are trying to compare to exact values.