XY Scatter Plot - Non-numeric data

[Guest]

Hi All!
I have been struggling with a problem when plotting data on an XY plot.
I have 2 columns of returns data (in % format) running down and I need to
plot them on the X-Y Axis. All is great when the data is present and numeric,
but my data changes each time I run a new report - I have non-numeric data in
some rows (blanks represented by "" from a formula saying something like
=if(a1="","",1%)
I was able to get around this for a while by creating and plotting a dynamic
named range that counts the number of numeric data points in the column and
adjusts accordingly but this does not work if there is a "" in the middle of
the data stream.
What i really need is a way for the chart to simply ignore non-numeric data
instead of assigning some random values that mess up my chart.
Does anyone know if there is a way to do this?
Thanks a lot in advance!
Stan

 

[Guest]

Hi,

Can you send me a sample, I'm not clear on what you want.

First is the XY chart include lines or just points? When you say ignore,
what do you want it to do - show an interpolated line or show a disconnected
line? When you it "assigns some random values" what do you mean? Usually it
would assign a value of 0 for entries containing "". If the X value is ""
but the Y value is 6, how do you want to handle it? or is it the case that
whenever X = "" Y=""?

(e-mail address removed)

 

[Jon Peltier]

Change the formula to:

=if(a1="",NA(),1%)

This puts an ugly #N/A error in the worksheet, but no point is plotted.

- Jon

 

[David Biddulph]

.... and of course you can use conditional formatting to hide the "ugly #N/A"
in the worksheet (by making the font the same colour as the background), if
you wish to do so.