Misunderstanding isblank

S

strawberry

If all the cells within the given range are blank, the following
formula evaluates to "#VALUE!"

=IF(AND(ISBLANK(H42:CS42)),"",CHAR(MAXA(Sheet1!32:32)))

I'd be grateful if someone can tell me either how to correctly rewrite
the formula or, failing that, how to supress the error. The error
doesn't affect overalll performance. It's just rather unsightly.

Any help greatly appreciated.
 
N

Niek Otten

The argument for ISBLANK can only be one cell, not a range

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| If all the cells within the given range are blank, the following
| formula evaluates to "#VALUE!"
|
| =IF(AND(ISBLANK(H42:CS42)),"",CHAR(MAXA(Sheet1!32:32)))
|
| I'd be grateful if someone can tell me either how to correctly rewrite
| the formula or, failing that, how to supress the error. The error
| doesn't affect overalll performance. It's just rather unsightly.
|
| Any help greatly appreciated.
|
 
D

Dave F

ISBLANK can be used over a range if it is used in an array formula.

=AND(ISBLANK(H42:Cs42)) resolves to TRUE if the range is blank and the
formula is entered with CTRL+SHIFT+ENTER.

However, even when entering your formula as an array formula I get a #VALUE!
error.

What are you trying to do with this formula?

Dave
 
D

Dave F

This formula works, however, entered as an array formula:
=IF(ISBLANK(H42:CS42),"",(MAX(Sheet1!32:32)))

I'm not sure what you're testing for with AND, CHAR, and MAXA....

Dave
 
S

strawberry

ISBLANK can be used over a range if it is used in an array formula.

=AND(ISBLANK(H42:Cs42)) resolves to TRUE if the range is blank and the
formula is entered with CTRL+SHIFT+ENTER.

However, even when entering your formula as an array formula I get a #VALUE!
error.

What are you trying to do with this formula?

Dave
Click to expand...

Switching to this seems to work:

=IF(COUNTA(H42:p42)=0,"",CHAR(MAXA(Sheet1!32:32)))

This is part of an Issue Sheet. It needs to keep track of the latest
revision letter (typically A-Z).
This formula simply looks at a range of cells to its right. If no data
is present, it evaluates to "". Otherwise it displays the highest
value present.
The corresponding worksheet (Sheet1) simply holds the letters as
(upper case) numeric values for comparison. Hope that makes sense.

Ta.
 
D

Dave F

Makes sense now, and if you want to avoid array formulas, COUTNA over the
range is the way to go.

Dave
 
Top