I am having a very frustrating problem with zero values in a conditional calculation.
I have a running balance column (E) with the custom number format of #,##0.00 ; [Red]-#,##0.00 ; ; when I use the simple calculation in the cells ie. E4 is =E3+D4, and the result is zero, the zeros are suppressed. The problem came when I changed it to a conditional calculation to avoid any cell results in future rows. The new formula being =if(A1>today(),,E3+D4), where A1 is every week day of the year.
The cells containing zero results prior to any values in D cells are working (not displaying zeros), but once the running balance column E was not equal to zero and then calculated back to zero, that cell and the cells after carrying a zero balance are displayed as 0.00
I have done many alterations to the format and formula to figure out why and all I can come up with is that the zero is being taken as a positive zero instead of just zero. (I formatted positives as blue and the 0.00 was blue)
When I change the formula back to straight addition (the same as in the conditional formula), the zeros are suppressed again.
Does anyone have any idea what is going in with this?
Best Answer
Unfortunately, without a sample spreadsheet, we need to start with a debugging exercise-- is this a formatting problem or a formula problem? Here's one way to approach the debugging.
Set up an "indicator" or verification cell formula to verify: is the running balance cell equal to Zero (TRUE or FALSE)? Is it greater than Zero (again, TRUE or FALSE)? If you can verify that the value, displayed as "0.00" is in fact greater than zero -- you can trace why you have a formula leaving you with a very small value and not zero. (You may have a very small rounding error, e.g. add 1/3 and subtract 0.33 -- balance is non-zero.)
If the cell check confirms the cell is equal to Zero, yet is being formatted incorrectly: tackle the in-cell formatting followed by the conditional formatting (if any). First, check carefully your custom number-formatting string. Reset your formatting string to a "known good" string -- the generic ones provided in the Format:Numbers box.
Finally, check conditional formatting by breaking down to one (1) condition at a time to isolate the condition which isn't working.