MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Question about ISBLANK function


Posted by Carol on January 11, 2002 9:01 AM

Can anybody help me on this?

Cell A1 of my spreadsheet has the formula =if(isblank(b2),1,0). The formula returns 1 if cell b2 is blank and a zero if cell b2 has a value. The problem I have is that cell b2 contains a formula. Sometimes this formula results in b2 appearing blank. But cell A1 always sees the formula and therefore always returns a zero.

How can I get A1 to return a 1 if B2 appears blank even though B2 has a formula in it?

Hope that makes sense!


Posted by Juan Pablo G. on January 11, 2002 9:06 AM

Try changing ISBLANK with LEN [NT]

Posted by Aladin Akyurek on January 11, 2002 9:07 AM

Carol --

Try:

=IF(LEN(B2),1,0)

instead.

Aladin

===========

Posted by Tom Urtis on January 11, 2002 9:12 AM

Aladin & Juan Pablo...

The way I interpret the question,
=IF(B2="",1,0)
May be the way to go.

When I tried the LEN function , I still got a 0 if the formula in B1 was something like
=IF(A3+A4=0,"",1)

Did I miss something?

Tom Urtis

Posted by Tom Urtis on January 11, 2002 9:16 AM

Typo...

The sentence
When I tried the LEN function , I still got a 0 if the formula in B1

should have read B2, but still I wonder about the question...

Thanks

T.U.

Posted by Carol on January 11, 2002 9:16 AM

LEN doesn't work because (m)

cell B2 ALWAYS has a formula in it. The formula will either put a specific number in the cell or leave it blank. I want A1 to determine if the cell is blank or if it has a value (while the function is always there).

Posted by Juan Pablo G. on January 11, 2002 9:18 AM

Re: Aladin & Juan Pablo...

Tom, i got 1 using in A1
=IF(LEN(B2),1,0)
in B2
=IF(A3+A4=0,"",1)

and putting some numbers in A3 or A4.

Juan Pablo G.

Posted by Juan Pablo G. on January 11, 2002 9:19 AM

Did you tested it ? worked for me. (NT)

Posted by Carol on January 11, 2002 9:21 AM

Thanks! I was overlooking the obvious. :) nft

Posted by Tom Urtis on January 11, 2002 9:23 AM

Re: Aladin & Juan Pablo...

But what if A3 and A4 have no formulas or values?
T.U.

Posted by Juan Pablo G. on January 11, 2002 9:30 AM

Re: Aladin & Juan Pablo...

I see what you mean. The original formula was

=IF(ISBLANK(B2),1,0)

then the proposed formula should be

=IF(LEN(B2),0,1)

or the one you proposed

=IF(B2="",1,0)

LEN = Not BLANK
, right ? so they should be "backwards"

Juan Pablo G.

Posted by Tom Urtis on January 11, 2002 9:36 AM

Agreed...thanks (nt)