Cell with 0 value showing blank

[mhessnm]

A coworker came to me with a problem, and I am stumped. She has a workbook with 12 sheets for each month of the fiscal year. In the sheet labeled June, she enters an amount under "This Month's Reimbursement Expenditures" (D14) In the next column, "Balance Remaining," is a formula that goes to the previous month sheet, May, and pulls the amount from E14, and subtracts June's D14. So the formula looks like this: =May!E14 - D14. In the next column is a formula that reads =IFERROR(E14/C14,0).

In June she entered $5,000.00, which the two formulas correctly calculate a 0 amount. On all the other sheets, those cells show a 0 amount. However, on June's the formulas return blanks.

I have looked for a reason. I have looked for settings both in File Options and even in Conditional Formatting, and have found nothing that explains why the June cells return a blank and not 0s. You can't even type a 0 into the cell...it returns blank. However, if there is any non zero balance, the cell shows that balance and the percentage in the next column. Only a 0 calculation brings a blank.

All the other sheets exhibit the same behavior. Any 0 balance and the formula returns a blank.

I am at a loss as to what is causing the 0s to become blank, as I have looked in every place I can think of, even cell formatting (they are formatted as currency with two decimal places and negative numbers having parentheses), and I haven't seen that behavior happen with a currency format).

I would appreciate any tips as to how to make those 0 calculations show as 0 amounts. Thank you! Screenshots below.

Screenshot: Shows formula, and blank cells in E14 and F14.

Same sheet when you change the amount in D14 by 1. Now the values appear as they should.

 

[mhessnm]

With that unchecked, you will never get to see a 0 in the cell.
However empty (blank) cells should not show a 0 unless they are not empty.

Fluff, you know what, there is a formula in the text cells pulling values (i.e. CPR Program) from the July worksheet across all the other worksheets. So if the formula is removed, that should solve the problem when that option is checked.

Thank you!

 

[Fluff]

Another option with that box checked is apply a cell format to certain cells only so they don't show a 0

 

[mhessnm]

Another option with that box checked is apply a cell format to certain cells only so they don't show a 0

Great, thank you! You've been a great help. I was wracking my brains to come up with an answer and being the Excel "expert" in our office, was feeling quite dumb and deflated not being able to solve the problem...and I figured it must be an easy solution. Thanks for providing me with the answer.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[RoryA]

there is a formula in the text cells pulling values (i.e. CPR Program) from the July worksheet across all the other worksheets

You've probably got a simple =Somecell type formula then. If you use =IF(somecell="","",somecell) syntax instead, you won't get 0 returned where the source cell is empty.

 

[mhessnm]

You've probably got a simple =Somecell type formula then. If you use =IF(somecell="","",somecell) syntax instead, you won't get 0 returned where the source cell is empty.

Thank you, Rory! Appreciate the tip!