Hello:

I have a number of cells that has an If statement. If it is false, then the
cell will be set to "". Is there a way to automatically clear the formula if
the evaluation is false?

The reason I want to clear the formula is when I create a graph based on the
cells with formula. If a cell has a formula, but no value, Excel still plots
that point and assumes it to be 0. If I manually delete those formula that
does not return a value, then Excel will exclude those plots in the plot even
though the empty range is part of a series.

If there is not a way to clear a formula the returns nothing, would it be
possible to suppress those points in the graph?

Thanks

Q

Make the result of the formula NA() instead of ""

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Q" <(E-Mail Removed)> wrote in message
news:CC01FF22-3DA1-45F9-906D-(E-Mail Removed)...
> Hello:
>
> I have a number of cells that has an If statement. If it is false, then
> the
> cell will be set to "". Is there a way to automatically clear the formula
> if
> the evaluation is false?
>
> The reason I want to clear the formula is when I create a graph based on
> the
> cells with formula. If a cell has a formula, but no value, Excel still
> plots
> that point and assumes it to be 0. If I manually delete those formula
> that
> does not return a value, then Excel will exclude those plots in the plot
> even
> though the empty range is part of a series.
>
> If there is not a way to clear a formula the returns nothing, would it be
> possible to suppress those points in the graph?
>
> Thanks
>
> Q

Hi,

It's better to suppress the points by returning #N/A instead of "" because
#N/A is ignored when charting. A su=implistics example of how to return NA

=IF(C2="",NA(),33)

Mike

"Q" wrote:

> Hello:
>
> I have a number of cells that has an If statement. If it is false, then the
> cell will be set to "". Is there a way to automatically clear the formula if
> the evaluation is false?
>
> The reason I want to clear the formula is when I create a graph based on the
> cells with formula. If a cell has a formula, but no value, Excel still plots
> that point and assumes it to be 0. If I manually delete those formula that
> does not return a value, then Excel will exclude those plots in the plot even
> though the empty range is part of a series.
>
> If there is not a way to clear a formula the returns nothing, would it be
> possible to suppress those points in the graph?
>
> Thanks
>
> Q

Thanks Niek.

"Niek Otten" wrote:

> Make the result of the formula NA() instead of ""
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Q" <(E-Mail Removed)> wrote in message
> news:CC01FF22-3DA1-45F9-906D-(E-Mail Removed)...
> > Hello:
> >
> > I have a number of cells that has an If statement. If it is false, then
> > the
> > cell will be set to "". Is there a way to automatically clear the formula
> > if
> > the evaluation is false?
> >
> > The reason I want to clear the formula is when I create a graph based on
> > the
> > cells with formula. If a cell has a formula, but no value, Excel still
> > plots
> > that point and assumes it to be 0. If I manually delete those formula
> > that
> > does not return a value, then Excel will exclude those plots in the plot
> > even
> > though the empty range is part of a series.
> >
> > If there is not a way to clear a formula the returns nothing, would it be
> > possible to suppress those points in the graph?
> >
> > Thanks
> >
> > Q
>
>

Thanks Mike! It works!

"Mike H" wrote:

> Hi,
>
> It's better to suppress the points by returning #N/A instead of "" because
> #N/A is ignored when charting. A su=implistics example of how to return NA
>
> =IF(C2="",NA(),33)
>
> Mike
>
> "Q" wrote:
>
> > Hello:
> >
> > I have a number of cells that has an If statement. If it is false, then the
> > cell will be set to "". Is there a way to automatically clear the formula if
> > the evaluation is false?
> >
> > The reason I want to clear the formula is when I create a graph based on the
> > cells with formula. If a cell has a formula, but no value, Excel still plots
> > that point and assumes it to be 0. If I manually delete those formula that
> > does not return a value, then Excel will exclude those plots in the plot even
> > though the empty range is part of a series.
> >
> > If there is not a way to clear a formula the returns nothing, would it be
> > possible to suppress those points in the graph?
> >
> > Thanks
> >
> > Q