Zero Values in worksheet functions/formulas.

[Carnadyne]

I'm having a problem here... recently upgraded to a new version of excel
and I've been looking in all the options can't seem to find a solution.

It seems whenever I enter a formula referencing a blank cell on another
sheet, instead of displaying a blank value like on the other sheet it
shows a "0". I still want to display 0 values where appropriate, so
selecting "do not show zero values" doesn't work for me.

I just want it to show a zero in cells formated at numeric, and blank
values (nothing) in cells formatted as either general or text or
whatever.

Any help would be appreciated,

Thanks.

Chris

 

[Carnadyne]

Yeah the single quote "" doesn't get rid of the zero's, I tried. Even i
I just put a formula in sheet1 such as "=Sheet2!B1" If B1 is blank... i
will display a zero on sheet1 instead of a blank.

The actual formula I'm using is:

=IF($D$1=(CHOOSE((WEEKDAY(TODAY(),1)),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
& "'s Tasks:",Test!A3,"")

Notice the single quote at the end of the formula... It displays
zero, and the destintion cell is formatted as text.

Did I misunderstand what you wrote? I'm fairly new to this stuff.

Thanks

 

[swatsp0p]

Yeah the single quote "" doesn't get rid of the zero's, I tried. Even i
I just put a formula in sheet1 such as "=Sheet2!B1" If B1 is blank... i
will display a zero on sheet1 instead of a blank.

The actual formula I'm using is:

=IF($D$1=(CHOOSE((WEEKDAY(TODAY(),1)),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
& "'s Tasks:",Test!A3,"")

Notice the single quote at the end of the formula... It displays
zero, and the destintion cell is formatted as text.

Did I misunderstand what you wrote? I'm fairly new to this stuff.

Thanks.

First, "=Sheet2!B1" _will_ return zero if B1 is blank. However
=IF(Sheet2!B1="","",B1) will return blank unless B1 is not blank, the
it will return whatever is in B1.

Second, I don't see a 'single quote' anywhere in your formula.
single quote is: [ " ] not to be confused with an apostrophe: [ ' ].
A double quote is two quotes without any spaces or text between them:
"" ] and is different from 4 apostrophes: [ ' ' ' ' ].

Your formula, as written, will return a blank when D1 does not contai
today's day. What isn't working for you

 

[BlueDaze]

Add an isblank formula into your if statement.

=if(isblank(a1)=true,"",a1