Calculations that create "blank" results


G

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
 
Ad

Advertisements

P

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

RSteffel said:
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.
 
C

Chris R. Lee

RSteffel said:
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
 
R

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.
 
Ad

Advertisements

R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top