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

[swatsp0p]

Generally, an IF function is used to test for blank cells and return a blank, as such:

IF(A1="","",YourFormulaHere,0)

The double quote ("") returns a blank cell if A1 is blank.

HTH

[Carnadyne]

Yeah the single quote "" doesn't get rid of the zero's, I tried. Even if I just put a formula in sheet1 such as "=Sheet2!B1" If B1 is blank... it 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 a 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 if I just put a formula in sheet1 such as "=Sheet2!B1" If B1 is blank... it 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 a 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, then it will return whatever is in B1.

Second, I don't see a 'single quote' anywhere in your formula. A 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 contain today's day. What isn't working for you?

[BlueDaze]

Add an isblank formula into your if statement.

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