Why is Excel doing this (displays zero instead of blank)?

Factual Questions
1

I’ve searched extensively and haven’t found any online help about this particular problem, so I hope the Excel gurus here can help.

I have an IF function following the standard structure of IF(Argument, resulttrue,resultfalse) where the false result is “” i.e., display nothing. So far so good. However, I want to append the results of that function with other things using the ampersand (&). The problem is that using ampersand makes the function display 0 instead of a blank, and this appears to override every method of hiding zeros- I’ve found nothing that works.

Any way to fix this?

2

So far, I’m not able to duplicate your problem. Are you sure your & data is not evaluating to 0? Can you post your data/formula?

3

Here’s the IF function:

=IF(C5="",B5*1,"")
i.e., if cell C5 is blank, go ahead and do the calculation on cell B5. If cell C5 is checked, display nothing. Cell C5 is used simply as a binary do/don’t do control.

That works fine. The problem is that adding anything using the ampersand, even something as simple as:

=IF(C5="",B5*1,"")&“text”
will, if C5 is checked, display the result:

0text

4

I can only replicate this behaviour if both C5 and B5 are blank. I think you’ll get the behaviour you want if you use a formula like



=IF(ISBLANK(C5),B5*1,"")&"text"
5

That didn’t help but I think I know what’s going on now: I used the Advanced Options to make all zero results blank. However, that applies to the entire cell; if there’s anything else there, everything is displayed; in this case the result of B5*1 when B5 is blank.

OK, back to the spreadsheet, I’ll see if there’s another way, but so far all formatting to hide or eliminate zeros seems to require that that be the only contents of the cell.

6

Note there is a numerical 0, a text 0, and a “null” which is nothing.

7

Posted too soon, edited too late: I believe this might be closer.



=IF(ISBLANK(C5),TEXT(B5*1, "#"),"")&"text"

This will, however, show just “text” if B5 happens to be 0. If B5 is any other value, say 5, you’ll get “5text”. Is that correct behaviour?

C5 values down the first column, B5 values down the rows:




C5           B5:| Blank |   0    | Other number
Blank           | text  |   text | text 
0               | text  |   text | text
Other number    | 2text |   text | text
8

Sorry, one last update… experimentation breeds multiple posts, it would seem! I also think I’ve made a mistake in the table in my last post, but it’s such a pain that I’m going to stop trying to make tables!

If you want to avoid the 0 behaviour for B5, you could use something like this:



=IF(ISBLANK(C5),IF(B5=0, "0", TEXT(B5*1, "#")),"")&"text"
9

Yes, I want to not display zeros, so this rather than your following post would be it. I’ll confirm there isn’t anything I’m overlooking but By Jingo, I think that’s it!