= in formula give 0 or nothing 1

[christhedonstar]

Hi,

If I set a cell to have the formula =A1 on a sheet and A1 has nothing in it I get nothing.

If I set a cell to have the forumla =A2 on the same sheet and A2 has nothing in it I get 0.

Does anyone know why there is this inconsistent behaviour? Both A1 and A2 having nothing in and are formatted the same way but I get different results in each case.

Thanks in advance,

Chris

 

[boulder38]

What version are you using?

ive got 2007 and i get 0 whichever cell i do it too.

Luke

 

[SkipVought]

Hi,

Are you absolutely sure that A1 has NOTHING in it? A SPACE is NOT NOTHING!

select A1 and hit your DELETE key.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[boulder38]

indeed!

a space is a character and that follows through to the cell, just tried and have that!

Are you trying on a new sheet or one that has info in it already

Luke

 

[AnotherHiggins]

In anticipation of your next question (once you realize that A1 is NOT 'empty'), if you want the formula to return something that looks like an empty cell, then try this:
[tab]=if(isblank(A1), "", A1)
or
[tab]=if(A1 = "", "", A1)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[christhedonstar]

Trying in a sheet that has info - it appears you get 0 when the cell is empty and nothing showing when you have "" in a the other cell or a formulae in the other cell that return "".

I usually use len to determine whether cell is value is blank.

Thanks for the replies.

 

[Gavona]

While len(a1)=0 works why not isblank(a1)?

Gavin

 

[dgillz]

Make sure the 2 cells are not formatted differently.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[SkipVought]

dgillz,

How would different formatting affect this problem?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GSCaupling]

_($* #,##0.00_);_($* (#,##0.00);_[b](* ""??_)[/b];_(@_)

The custom format above produces a blank cell for zeroes and spaces.

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[SkipVought]

Thanks!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[christhedonstar]

Gavona, no real logical reason except I feel more comfortable with it and its consistent with how I'd code it in vba.

 

[AnotherHiggins]

I usually use len to determine whether cell is value is blank.

That doesn't work.

If you have a formula in A1 that returns "", then:
[ul][li]=Len(A1)[/li]
[ul]returns 0[/ul]
[li]=A1=""[/li]
[ul]returns TRUE[/ul]
[li]=IsBlank(A1)[/li]
[ul]returns FALSE[/ul]
[/ul]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.