1

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?

Improve this question

1 Answer 1

Reset to default
2

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.

  1. 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.)

  2. 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.

Improve this answer
4
  • Thank you. The simple addition calculation (the two numbers in the cells being added are -156.23 and 156.23) alone in the formula has to equal zero as it is suppressed by the format and the resulting cell is blank. However, when I use the same exact calculation within a logical IF formula, it shows 0.00, following the positive number format of the custom format. Why would the same simple arithmetic calculation being within an IF formula change the actual result in some way?
    – Michelle Schulmann
    Commented Jul 30, 2015 at 15:38
  • If you have one cell with each method, does a comparison of the two "zeroes" (like =G1=H1) result in TRUE or FALSE?
    – whiskeychief
    Commented Jul 30, 2015 at 18:24
  • I gave up figuring out why the result of a calculation within an IF formula is different in some way than the simple calculation on its own. I went around it by using TRUNC. Thank you for your time though; I appreciate it.
    – Michelle Schulmann
    Commented Jul 30, 2015 at 18:53
  • @MichelleSchulmann still, I'd like to see an example spreadsheet if you can sanitize it of any personal data, or confirm if you were able to verify that Excel agreed that =C10=D10 was TRUE or FALSE.
    – whiskeychief
    Commented Aug 4, 2015 at 11:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .