Conditional Formatting shows wrong color!

[brncao]

It worked previously, but all of a sudden it's showing the wrong color. Before you ask, there are no percentages and fractions. Just additions and subtractions of cash. I'm trying to do this: if A = B, shade with light green fill with dark green text; if A <> B, shade with light red fill with dark red text.

But... when A = B, I'm getting a light red fill and dark red text. Why? A <> B works fine.

I'm just staring at the Conditional Formatting Rules Manager and can't wrap my mind around this.

 

[JoeMo]

Are A & B numbers or text?

 

[brncao]

They're numbers.

I found the culprit. The calculated cell is, in reality, $71,571.0799999998, which is not equal to $71,571.08. What the hell? There's only two decimal places and I'm just doing basic adding and subtracting. What's going on?

 

[brncao]

They're numbers.

I found the culprit. The calculated cell is, in reality, $71,571.0799999998, which is not equal to $71,571.08. What the hell? There's only two decimal places and I'm just doing basic adding and subtracting. What's going on?

71123.13

1445000

1444552.05

=A1+A2-A3

<tbody>
</tbody>

Remember to increase decimal places.

 

[brncao]

Solved it. Apparently it's a floating-point arithmetic error as explained here https://support.microsoft.com/en-us/kb/78113?wa=wsignin1.0

I resolved it by using the round function to round it to two decimal places.

 

[deletedalien]

Whenever i do conditional formatting i usually avoid this by "locking" the ranges say for example you are doing 1 criteria 0-10 and second criteria 11-20 and a third one 21 and over

i do between 0 to 10.0000001

then between 11 to 20.0000001

then lastly

between 21 - 1000000000000


or something along those lines depending on the scnenario

Juuuuuust to make sure

 

[JoeMo]

They're numbers.

I found the culprit. The calculated cell is, in reality, $71,571.0799999998, which is not equal to $71,571.08. What the hell? There's only two decimal places and I'm just doing basic adding and subtracting. What's going on?

As you have discovered, Excel has 15-digit precision so when comparing number A to number B even though they may appear to be equal when viewing the cells they are in, if they are not equal out to the limit of Excel's precision then A=B is false. Better to use something like Round(A,2) = Round(B,2) to handle CF for number comparisons.