Problem with ISBLANK Function

[Razoo]

Hi,

I have an Excel 2003 worksheet and I wish to perform certain functions if a
range of cells are ALL blank (empty).

I am trying to use the ISBLANK function, but am getting varying results.

Can the ISBLANK function be used to check a range of cells simultaneously?

e.g. =IF(ISBLANK(L11:M21),"Blank","Not Blank")

I am using the following formula in Conditional Formatting, and it seems to
work fine:

=AND(LEFT(L49,1)="G",ISBLANK(L11:M21))

(the first part of the formula looks for a word in L49 beginning with "G",
the second part checks that none of the cells contain data)

However, this Conditional Formatting is actually set in cells L11:M21.

When I call ISBLANK(L11:M21)) from another cell, ISBLANK returns False,
even though there is no visible data in any of the cells L11:M21.

Also, if I call ISBLANK from another cell, but only look at any ONE of the
cells in the range L11:M21 (e.g. L12), then ISBLANK returns True. As soon
as I look at a group of two or more cells in the range (e.g. L12:L14),
ISBLANK returns False. It's not making a lot of sense!

Does the ISBLANK function see a cell with conditional formatting set as a
non-blank cell?

The cells that I am checking with ISBLANK have data validation set so that
they can either be blank (empty) or contain a number between -24 and +24.

Is there a better function I can use to check whether these cells have any
data in them? I only need to know if any one of the cells has data in it.
I need to do the whole range L11:M21 at once, I don't want to test the cells
individually.

TIA,

 

[JBeaucaire]

Try this:

=IF(COUNT(L11:M21)=0, "Blank", "Not blank")

 

[Razoo]

Try this:

=IF(COUNT(L11:M21)=0, "Blank", "Not blank")

Thanks, that works fine.

Any idea why I was getting such inconsistent results with ISBLANK?

 

[JBeaucaire]

ISBLANK I find to be one of the "misnamed" functions. We think that means
"appears to be blank" but what it really means is "ISEMPTY". If you have a
formula in a cell returning a null value, the cell looks empty, but is not.

I find very little use for ISBLANK. I usually need functions that return a
null count for cells with a null value. COUNT() does that.

 

[Joe User]

ISBLANK I find to be one of the "misnamed" functions. We think that
means "appears to be blank" but what it really means is "ISEMPTY".
[....] I find very little use for ISBLANK.

I concur. But I'm not sure that explains the "inconsistency" that Razoo
observes.

The real point is: ISBLANK(L11:M21) does __not__ check "if a range of cells
are ALL blank (empty)", even if Razoo intends to count truly empty cells (no
formula and no constant).

In the context of Razoo's formula, ISBLANK(L11:M21) returns TRUE or FALSE
solely based on the first cell of the range, L11.


----- original message -----

ISBLANK I find to be one of the "misnamed" functions. We think that means
"appears to be blank" but what it really means is "ISEMPTY". If you have
a
formula in a cell returning a null value, the cell looks empty, but is
not.

I find very little use for ISBLANK. I usually need functions that return
a
null count for cells with a null value. COUNT() does that.

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

----- previous posting -----

 

[Razoo]

JBeaucaire wrote:
ISBLANK I find to be one of the "misnamed" functions. We think
that means "appears to be blank" but what it really means is
"ISEMPTY". [....] I find very little use for ISBLANK.

I concur. But I'm not sure that explains the "inconsistency" that
Razoo observes.

The real point is: ISBLANK(L11:M21) does __not__ check "if
a range of cells are ALL blank....

Thanks for the information guys.

I understand ISBLANK now, although I don't see much use for it.

COUNT is working just fine.