Only plot scatter points that have both an X and a Y value (2007)

**JayUSA** · 06-14-2009, 06:36 PM

Hello Everyone,

I'm wondering if there is a clever way to configure a scatter chart so that it only plots points that have both an expressed X value and an expressed Y value.

Right now, I have two columns -- one for X values and the other for Y values.

For various reasons, many of my data points have an empty cell ("") for their X value, or an empty cell for their Y value.

Since I want to trendline my scatter chart, and for other reasons, I'd like to ignore these un-plottable data points entirely. Said differently, the empty cells do not represent a zero value, and I don't want to treat them as if they do.

If my data were static, I could simply Sort the data to separate the wheat from the chaffe.

But my data is highly dynamic -- small changes to the inputs change which data points lack an expressed X or Y coordinate.

Since the X and Y data columns are part of a data table, I have tried to use the "Number Filters" at the top of each column. But -- annoyingly, unless I misunderstand something -- the Number Filters do not refresh to reflect changes in the table content. Thus, if I filter out the empty cells, the scatter plot is correct, but as soon as the values in the table columns change, the scatter plot is incorrect again.

So... summing up.. I'd like a way to dynamically (or automatically) chart only those points which have both an expressed X and an expressed Y value.

Ideas, anyone?

Cheers,

Jay

**JayUSA** · 06-14-2009, 07:24 PM

Hello,

While I would still be interested in any ideas regarding the above, I'm going to mark this thread as "solved". That's because I think that the most elegant solution is to find a way to automatically refresh the Autofilter which can filter out nonvalid plotting points.

So, I will repose this question in an "Automatically Refresh Autofilter" thread, or revive an old thread addressing this issue.

Cheers,

Jay