Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A scatter plot with a 2 column data source | Charts and Charting in Excel | |||
XY Scatter Plot - Change Labels From Numeric To Text Programmatica | Charts and Charting in Excel | |||
Can I copy x-y scatter plot data direct from one plot to another? | Charts and Charting in Excel | |||
Custom data labels on a scatter plot | Charts and Charting in Excel | |||
scatter plot & label for a data point | Charts and Charting in Excel |