XY Scatter Plot - Non-numeric data
 

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

XY Scatter Plot - Non-numeric data
 

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=""?


--
Thanks,
Shane Devenshire


"Stan" wrote:

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

XY Scatter Plot - Non-numeric data
 

Change the formula to:

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


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

XY Scatter Plot - Non-numeric data
 

.... 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.
--
David Biddulph

"Jon Peltier" wrote in message
...
Change the formula to:

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

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

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