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.

 

[Alex Blakenburg]

You mention that you have checked conditional formatting does that meany you have also checked the format (custom) directly applied to the cell. Can you show us the Number Format screen with the cell selected.
In fact what happens if you copy the formatting from a cell in a sheet that works to the cell that doesn't work ?

 

[StephenCrump]

Did you also check in Advanced Options, where you can suppress zero values from being displayed in particular worksheet(s)?

 

[mhessnm]

Did you also check in Advanced Options, where you can suppress zero values from being displayed in particular worksheet(s)?

View attachment 114797

Hi Stephen,

Yes...that was unchecked in her spreadsheet, but checking it means that every blank cell, including those where text is is to be typed (expenditure category, for example), shows a zero. I also checked to see if there was some kind of conditional formatting (none), data validation (none) and some kind of VBA code (none). I tried using the format painter to make the cells appear like the others, and that didn't work. All she wanted was to show a 0 balance in the cell and 0 in the remaining percent, and for the life of me I can't figure out why the zeros become blanks.

Michael

 

[MARK858]

Can you show us the Number Format screen with the cell selected.

Can you show use the Number Format screen as Alex requested in post 2 please, also did you also try the below from the same post (Copy/Pastespecial - Formats rather than the Format painter)?

In fact what happens if you copy the formatting from a cell in a sheet that works to the cell that doesn't work ?

 

[felixstraube]

You can select the cell and go to Editing/Clear/Clear Formats:

Do you see a value after that?

 

[mhessnm]

Can you show use the Number Format screen as Alex requested in post 2 please, also did you also try the below from the same post (Copy/Pastespecial - Formats rather than the Format painter)?

Hello Mark, sorry I did not see Alex's post - I was only alerted to Stephen's post on email and not Alex's on email and responded to it from the email.

The format is currency format. Here's the screenshot:

The number format, if I go to custom, is S#,##0.00_);(S#,##0.00).

Using the format painter on a cell with the desired format in the same sheet, or on a new workbook sheet and copying to the cell doesn't change anything. Neither does copying and pasting only formatting using pastespecial change anything in that cell.

 

[felixstraube]

You can select the cell and go to Editing/Clear/Clear Formats:

View attachment 114818

Do you see a value after that?

Did you try this?

 

[felixstraube]

Or can you share the file?
Or just the sheet you have problems with, without data?

 

[Fluff]

Yes...that was unchecked in her spreadsheet, but checking it means that every blank cell, including those where text is is to be typed (expenditure category, for example), shows a zero.

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.