clear formula that does not return a value automatically?
 

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

clear formula that does not return a value automatically?
 

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Q" wrote in message
...
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

clear formula that does not return a value automatically?
 

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

clear formula that does not return a value automatically?
 

Thanks Niek.

"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Q" wrote in message
...
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

clear formula that does not return a value automatically?
 

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