Forum Discussion
Cannot understand why Conditional Formatting is not functioning correctly
It could be rounding error if you calculate values by formula
SergeiBaklan I believe you are exactly right by this. In evaluating the formula for these cells I see that the result is a long amount after the decimal (ie: 41.65 = 41.6499999999942)-see attached screen snip shot. I have tried to figure out why this is even calculating as such a long decimal number when the amount is in dollars and cents. I am at my wits end with this.
If you format in dollars and cents, or apply any other format, that doesn't change the value in the cell, it only changes the way as this value is shown.
SergeiBaklan Thank you for your prompt reply. The cells have calculations in them, however, they aren't rounded. They are basic math calculations for dollar values with no rounding. I have included a screen shot of the cell formulas along with text comments for your reference. The values are no different than the ones in the sheet that the Conditional Formatting works correctly. Note also, that on the sheet where this is not working correctly in the cells I have pointed out, the conditional formatting does work in all other cells on this same worksheet with the same formatting values (ie: the formatting doesn't work correctly for the month of January, however, for the month of February the formatting works correctly).
The rounding error means that Excel calc engine makes some rounding performing the calculations since it keeps only 15 digits for calculated numbers. For example, = 1*(0.5-0.4-0.1) doesn't return zero, it returns -2.77555756156289E-17. Which could be shown as zero if you apply some formatting. That's not only Excel, any app has similar rounding errors.
You may simply check you value, if it is for example in A1 as =A1=B1 if B1 is empty cell. If FALSE that means A1 is not zero.
To avoid such error you may use ROUND(A1,10) or like.
SergeiBaklan Thank you soooooo very much! I did do what you suggested. I'm not sure why this is the only part of this worksheet that does this because the rest of the sheet has the same calculations and doesn't. However, it doesn't matter at this point. You saved me from going absolutely crazy over this. I wasn't going to rest until I figured it out and you have saved me so much more time. I am absolutely appreciative of your help. Please know that. You have a wonderful weekend! You are a godsend! 🙂
