Cell evaluating to zero displaying as blank

Topic

New Reply

Simple version: There may or may not be a value in cell A50. Cell B50 contains the formula =IF(A50=””, “”, A50). The desired result is that B50 stays blank if A50 is blank – otherwise it displays the same result. It works except when A50 equals zero. Then B50 displays as blank. (This is simplified from a pre-existing worksheet in a pre-existing workbook in Excel XP.)

The “Zero values” option is checked. (Besides, A50 is displaying zero properly.) There’s no conditional formatting that I can find. And a test (in C50) confirms B50 contains the empty string “”! So why does =IF(A50=””, “”, A50) evaluate to “” when A50 = 0?

As an added puzzle, if I try this on a newly created worksheet in the same workbook, it works as expected. That is, if B1=IF(A1=””, “”, A1), then B1 displays 0 if A1=0.

Any suggestions?

Reply | Quote

Replies

Has a custom format been applied?

Reply | Quote

That’s one I forgot to mention. I tried both Fixed (1 decimal place) and General and neither changed anything.

Reply | Quote

….. if I try this on a newly created worksheet in the same workbook, it works as expected. That is, if B1=IF(A1=””, “”, A1), then B1 displays 0 if A1=0.

If you copy the data down to Row 50, do you get the desired result? If so, how old is the workbook & does the worksheet require rebuilding? HTH

Reply | Quote

Yes, copying down to Row 50 – in the new worksheet but in the original workbook – still does give the desired result.

I’m not sure how old the workbook is – several years old at least and used in Excel 2000 when I first got into this office.

Worksheet rebuilding is not a concept I’m familiar with. Is it basically redoing the worksheet in a brand new workbook? Or something else?

Reply | Quote

I ended up basically copying everything to a new workbook. Had to weed out some external references the copying introduced, but it still didn’t take very long. And now the formulas work as expected. So some cruft that had accumulated from years and versions past? (It’s possible this thing is descended from a Lotus 1-2-3 version I did over 15 years ago in another office.) Don’t know for sure and I’m not going to worry about it anymore – although if anybody still has a suggestion, I’m still a little curious.

Reply | Quote

What you’ve experienced can happen – in some cases – with a workbook that is onlya few years old. Hopefujlly, you were able to remove the external references copying introduced by using the method described here.

Reply | Quote

Yep, that method you linked to is more or less what I did. There was only one link that the Edit -> Links process didn’t work for and that turned out to be buried in a chart.

So this was all educational – I wasn’t aware you could suffer this sort of slow corruption in a spreadsheet. Had I thought about it, I would have assumed they either work as always or don’t work at all. Something to be aware of in the future.

Reply | Quote