Forum Discussion
Conditional Formatting error when value is between 99.99 and -100
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.
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.
- This worked, thank you so much - you've saved my sanity!
