Require Scatter Chart that doesn't Plot Zero or "-"

**R_S_6** · 03-10-2010, 11:33 AM

Hi All,

I have a scatter chart that is used to display some x, y data in my spreadsheet. This x,y data is calculated through if statements from other data. If the a value is outside my limits of the If statement a "-" is created in the x or y cell.

I am trying to find a way that with the two columns for x and y highlighted, the plot will only show data that does not include a "-". At current if the fields are x= "-" and y = "-" it plots the y value as zero with increasing x.

I have included an example to explain my point. I only want the graph to display the non zero and non "-" values from the x, y data.

Any help on the above would be appreciated!

**6StringJazzer** · 03-10-2010, 11:44 AM

This is a "poor man's" solution but I have used it and it certainly works. Instead of generating "-", generate a -1. Then on your chart, modify the scale for each axis to have a minimum of 0. Then the negative points do not appear on the plot.

Alternatively, if you have no actual data that has a 0 (there is none in your example), you can take your existing data and just modify the scales to start at something higher than 0.

**6StringJazzer** · 03-10-2010, 11:50 AM

On second look at your workbook, I see that the nonnumeric cells seem to screw up how the scatter chart is plotted. That is, it doesn't use your actual x values to plot, it uses the sequence number. I am guessing that is how Excel responds to finding text data when doing a chart. In that case I would emphasize that my advice above will solve it.

**R_S_6** · 03-11-2010, 04:22 AM

Thanks '6StringJazzer' - I'll take your advice and have a go at that now!
Thanks for your help!
Mark

**teylyn** · 03-11-2010, 05:20 AM

RS6,

you can use dynamic range names to define the populated cells of your sheet as the range to be graphed.

Range name

ChartX =INDEX(INDEX(Sheet1!A:A,MATCH(MIN(Sheet1!A:A),Sheet1!A:A,1)):INDEX(Sheet1!$A:$A,MATCH(99^99,Sheet1!$ A:$A,1)),0)

ChartY =OFFSET(ChartX,0,1)

Then define the data series as

Series X values =Example_File.xls!ChartX
Series Y values =Example_File.xls!ChartY

This will grow and shrink as you add values to your columns A and B. Empty cells in the first few rows of A and B will be ignored in the chart.

see attached

hth

**martindwilson** · 03-11-2010, 05:44 AM

or perhaps
edit i forgot you said 0 or - so perhaps use =IF(OR(A2={"-",0}),NA(),A2)
or just do as andy suggests below

**Andy Pope** · 03-11-2010, 05:46 AM

Or you can use =NA() instead of -

**R_S_6** · 03-11-2010, 07:56 AM

Thank you all for your input!
The '=NA()' did the trick and the graph now only plots the required numbers. However my =SLOPE() function now no longer works as it sees '#N/A' in the data set. Is there any way to get around this. I.e. such that the slope function only looks for the cells containing actual values. ( LINEST() function also affected by the '#N/A's)

I have a feeling that the methodology posted by 'teylyn' may solve this however looking at the re-uploaded example I cannot see how the 'dynamic range names' work. They are not something I have used before.

Ideally if there is a way to modify the SLOPE() function formula this would be ideal.

Cheers Guys - Really appreciate it!
Mark

**Andy Pope** · 03-11-2010, 08:01 AM

simply use a different set of data for the chart than for the SLOPE/LINEST functions.

**R_S_6** · 03-11-2010, 08:34 AM

Okay - cheers Andy - I'll do that and hide the columns!

Mark

**Andy Pope** · 03-11-2010, 08:37 AM

Make sure the chart has the option to display visible cells only turned off.

Select the chart and then use Tools > Options > Chart > Plot visible cells only.