ISBLANK Global setting?

[Alan F.]

Does anybody know if there is a global setting etc. or code that I can
set so that I do not get the #VALUE! errors whan one of my formulae
references a blank cell? A sort of "OnError" command.

I know that I can modify all my formula with
IF(ISNUMBER(A1),A1*(1-b2/100),"") but then all my simple formulae
become more difficult to read, longer to create and more prone for
errors.

=A1*(1-b2/100) is a lot easiar to read and trouble shoot
then =IF(ISNUMBER(A1),A1*(1-b2/100),"")

What I want is for the system to just return a blank if there is a
blank in the data cells.

In hope

Alan F.

 

[Frank Kabel]

Hi
AFAIK there's no such setting. you you have to include the
error checking on your own
Frank

 

[DNF Karran]

The #VALUE! error is probably being caused by a reference being made t
a range that is text or even a zero length string. It may be wort
looking to correct the problem at this stage eg by use of the
function that will convert any text to 0's.

Dunca

 

[JE McGimpsey]

Spend an hour or so with such formulae and they will no longer be very
difficult to read, or take appreciably longer to create.

As far as more prone to errors, I disagree entirely. THe discipline
involved in designing a worksheet that traps errors appropriately will
help you to avoid design errors that silently give you bad results.

It won't prevent you from screwing up - if you mindlessly start each
formula with something like IF(ISNUMBER(A1),...), it won't help. If you
only use that construction for situations where A1 is *expected* to
sometimes be blank, however, then you'll know there's a problem when you
deliberately *don't* use that error trap (i.e., because A1 should never
be blank), and you get the #VALUE! error. If instead you'd been able to
apply a global band-aid, your model might fail without warning you.

 

[Alan F.]

Spend an hour or so with such formulae and they will no longer be very
difficult to read, or take appreciably longer to create.

As far as more prone to errors, I disagree entirely. THe discipline
involved in designing a worksheet that traps errors appropriately will
help you to avoid design errors that silently give you bad results.

It won't prevent you from screwing up - if you mindlessly start each
formula with something like IF(ISNUMBER(A1),...), it won't help. If you
only use that construction for situations where A1 is *expected* to
sometimes be blank, however, then you'll know there's a problem when you
deliberately *don't* use that error trap (i.e., because A1 should never
be blank), and you get the #VALUE! error. If instead you'd been able to
apply a global band-aid, your model might fail without warning you.

So that's a "no" then ;-)

Regards Alan F.

 

[Alan F.]

Does anybody know if there is a global setting etc. or code that I can
set so that I do not get the #VALUE! errors whan one of my formulae
references a blank cell? A sort of "OnError" command.

I know that I can modify all my formula with
IF(ISNUMBER(A1),A1*(1-b2/100),"") but then all my simple formulae
become more difficult to read, longer to create and more prone for
errors.

=A1*(1-b2/100) is a lot easiar to read and trouble shoot
then =IF(ISNUMBER(A1),A1*(1-b2/100),"")

What I want is for the system to just return a blank if there is a
blank in the data cells.

In hope

Alan F.

Many thanks to all for your replies.

Kind regards Alan. F