Formula result as real empty/blank cell

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