How can I get a formula result to be an actual empty cell (usually when using
the IF function). I know that "" of NA() is an option to get a Blank cell,
but this results in errors when the result of this formula is used in another
function.

Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would
like the formula to result in an EMPTY (or Blank) result. Now I have used ""
which gives no visible result in the formula cell, but when I multiply this
formula result with another value it will result in #VALUE!

It seems to be one of the shortcommings of Excel!?

Regards JB

"Excelerate-nl" wrote:
How can I get a formula result to be an actual empty cell (usually when using
the IF function)...

Don't think this is possible

Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would
like the formula to result in an EMPTY (or Blank) result. Now I have used ""
which gives no visible result in the formula cell, but when I multiply this
formula result with another value it will result in #VALUE!

Instead of using =IF(isblank(a1);"";A1*B1)

Perhaps putting it as: =IF(A1="";"";A1*B1)
would suffice
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

In the downstream formulas where you are getting #VALUE errors, either use
ISERR() or ISERROR() just as you used ISNA() in the first one. That will
handle those cases for you.

"Excelerate-nl" wrote:

How can I get a formula result to be an actual empty cell (usually when using
the IF function). I know that "" of NA() is an option to get a Blank cell,
but this results in errors when the result of this formula is used in another
function.

Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would
like the formula to result in an EMPTY (or Blank) result. Now I have used ""
which gives no visible result in the formula cell, but when I multiply this
formula result with another value it will result in #VALUE!

It seems to be one of the shortcommings of Excel!?

Regards JB

Dear JLatham,

Thanks for responding. Your suggestion will solve downstream errors, but
when the outcome of the formula relates to a lot of other formulas it is not
a nice scenario.
I guess it's just not possible to define a result of a formula as a blank
cell (would be a good suggestion for Microsoft to have something as BLANK(),
similar to NA())

Regards,

JB

"JLatham" wrote:

In the downstream formulas where you are getting #VALUE errors, either use
ISERR() or ISERROR() just as you used ISNA() in the first one. That will
handle those cases for you.

"Excelerate-nl" wrote:

How can I get a formula result to be an actual empty cell (usually when using
the IF function). I know that "" of NA() is an option to get a Blank cell,
but this results in errors when the result of this formula is used in another
function.

Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would
like the formula to result in an EMPTY (or Blank) result. Now I have used ""
which gives no visible result in the formula cell, but when I multiply this
formula result with another value it will result in #VALUE!

It seems to be one of the shortcommings of Excel!?

Regards JB

Excelerate-nl wrote:
Dear JLatham,

Thanks for responding. Your suggestion will solve downstream errors, but
when the outcome of the formula relates to a lot of other formulas it is not
a nice scenario.
I guess it's just not possible to define a result of a formula as a blank
cell (would be a good suggestion for Microsoft to have something as BLANK(),
similar to NA())

Regards,

JB

"JLatham" wrote:

In the downstream formulas where you are getting #VALUE errors, either use
ISERR() or ISERROR() just as you used ISNA() in the first one. That will
handle those cases for you.

"Excelerate-nl" wrote:

How can I get a formula result to be an actual empty cell (usually when using
the IF function). I know that "" of NA() is an option to get a Blank cell,
but this results in errors when the result of this formula is used in another
function.

Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would
like the formula to result in an EMPTY (or Blank) result. Now I have used ""
which gives no visible result in the formula cell, but when I multiply this
formula result with another value it will result in #VALUE!

It seems to be one of the shortcommings of Excel!?

Regards JB