clear formula that does not return a value automatically?

[Q]

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

 

[Niek Otten]

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

 

[Mike H]

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]

Thanks Niek.

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Q]

Thanks Mike! It works!

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