Calculations that create "blank" results

[Guest]

For purposes of charting large amounts of data, I need to test for certain conditions, and then have the result appear as a blank cell. If I use "", or 0, the charted data shows up as zero. I really just want these data to not show up on the chart. And I need to use data formats that include lines, so using a negative number and then suppressing values less than zero will not provide the result I'd like

I think I need a Blank() function

Any ideas

Thanks

 

[Peo Sjoblom]

Replace 0 with =NA() or if in a formula like

=IF(A1>A2.300^2,0)


=IF(A1>A2.300^2,NA())

you can hide the result with conditional formatting if you dislike #N/A

--

Regards,

Peo Sjoblom

For purposes of charting large amounts of data, I need to test for certain

conditions, and then have the result appear as a blank cell. If I use "", or
0, the charted data shows up as zero. I really just want these data to not
show up on the chart. And I need to use data formats that include lines, so
using a negative number and then suppressing values less than zero will not
provide the result I'd like.

 

[Chris R. Lee]

For purposes of charting large amounts of data, I need to test for certain

conditions, and then have the result appear as a blank cell. If I use "", or
0, the charted data shows up as zero. I really just want these data to not
show up on the chart. And I need to use data formats that include lines, so
using a negative number and then suppressing values less than zero will not
provide the result I'd like.

I think I need a Blank() function.

You do, and its absence, discussed many time in this NG, indicates a
fundamental deficiency that may lead to doubts about the suitability of this
software for really serious applications.

The workaround in the post by Peo Sjoblom is neat, but it works around a
problem that should never have existed, even in the first version of XL.

Regards

 

[RSteffel]

Thanks. I've been looking for a fix for a long time, since
it seems intuitive that it should be possible.

BTW, using NA() will sort of work, but charting then uses
lines in inappropriate locations to connect unconnected
points. Oh well.

 

[RSteffel]

Thanks for the idea. It almost works, but with data sets
that use a line (in the chart), it ends up connecting
errant points. But now I may be able to selective erase the
results I need. Thanks again.