Forum Discussion
Cannot understand why Conditional Formatting is not functioning correctly
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! 🙂
Michael1105 , you are welcome, glad to know you sorted this issue out. Take care!