Conditional Formatting error when value is between 99.99 and -100

[crystalrosepa]

Ok I feel like I'm going nuts trying to solve this issue. I have an expense tracker I created and for my final balance, I have a check cell in place to make sure all my numbers are calculating correctly. I have conditional formatting set on it so if it doesn't match my final balance, it turns red. Works great most of the time, but for some reason, if the balance is between 99.99 and -100, it turns red even when they match. My final balance is in cell X27 and the check cell is in X28. The formula in X27 is: =X26-R27+V27 (it takes the balance from the line before, subtracts the expense from that line and adds the deposit from that line. Note: no line ever actually has both an expense and deposit). The formula in X28, the check cell, is: =(X3+V28)-R28 (it takes the starting balance and adds the total deposits for the page, then subtracts the total expenses for the page).

I've tried multiple different conditional formatting formulas including:

• Cell Value <> $X$27
• =$X$28<>$X$27
• =IF(X28<>X27,1,0)

I've tried variations of these with and without $ and still have the same result. Both values are in merged cells, but I don't see how that could cause this specific problem? Again, it works every other time except when the values are between 99.99 and -100. Oh and if it's a zero value it works fine too.

It's pretty rare for these values to not match, so if I can't get it resolved, I could always remove the conditional formatting and call it a day, but it's almost like a vendetta now because there's no reason it should have this weird problem unless there's some glitch with Excel I'm unaware of. If anyone knows how to fix it, I would appreciate it. I tried to give as much info as I could think of (I don't have permission to upload images, sorry), but if I can provide any other info, please let me know!

[#2 · Accepted solution]

@crystalrosepa This is a rounding issue. Excel sometimes does weird things at the 15th/16th decimal. Much has been written about in this forum and elsewhere on-line. About how the Excel calculation engine works and sometimes fails. Mostly irrelevant for your daily use in accounting. Important to remember is, that when this obvious error occurs (two numbers that should be the same are not recognised as such by Excel), you need to do some rounding. Either in your formulas or in the CF rule.

Did some experiments and came up with these numbers (using your formulas) in X27 and X28.

Note the rounding variance at the far end. Applying the following CF rule will still accept both numbers as equal, and thus NOT flag it as an error.

=ROUND(X27,2)<>ROUND(X28,2)

See if it works in your workbook as well.

[#3]

This worked, thank you so much - you've saved my sanity!