Forum Discussion
Cannot understand why Conditional Formatting is not functioning correctly
Attached you will find a screen shot of two exact conditional formatting. One is working correctly on one worksheet and the other will not work correctly. Worksheet #2 is the one in question. On this worksheet I have the cell circled and I want the cell to show Blue if the amount is zero, yet it turns green when the value is zero.
I have completely cleared all formatting and reentered the information from scratch, I have copied and pasted the cells formulas and conditional formatting from the worksheet that it is working correctly and it does not work correctly on the worksheet I am pasting it to.
Worksheet #1 shows the same exact conditional formatting and it is functioning correctly. Can someone tell me what I am missing or overlooking here?
7 Replies
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.
