Point limits for scatter diagrams

[woodenschu]

I have a relatively small spreadsheet which contains multiple X and Y
coordinates for a scatter plot that represents defects over time. The
data and the graphs are to be located in separate worksheets.
However, none of the (X,Y) coordinates are in adjacent cells, and few
of the X or Y coordinates are in adjacent cells.

So, when I use the chart wizard to create the scatter plot, I have to
use the following format to define the series of X and Y points:

'Source Data'!$G$1,'Source Data'!$G$5,'Source Data'!$G$6,'Source Data'!
$G$11, etc.

However, there seems to be a limit as to the length of information you
can use to define data points using the wizard. When I get to a
certain point, I simply can't enter any more data. If I use the CNTL-
Click method to enter data points, when I get to a certain point, the
data points I had entered are erased and the wizard starts new using
the next point.

So, how do I get around the fact that I have say, 20 data points, but
I can only enter 8 or 9 before I hit this limit? Is there a way I can
define a name for the points ( Insert -> Name -> Define in 2003 ) and
then use that name to reference the X and Y points individually?

Any help is appreciated.

Thanks!

 

[ShaneDevenshire]

Hi,

That particular limit is a formula length limit although scatter chart do
have low limits on the number of points, that's not the problem here.

If there is some common factor in one of the columns of your data area, you
can apply an AutoFilter to display only those rows you want. Build your
chart from the filtered data.
Then with the chart selected choose Tools, Options, Chart, Plot visible
cells only.

You may need to add an extra column to your data area with a formula which
returns a unique entry to help you filter the data, but in general you can
find something that works. For example, suppose the rows you don't want to
see are blank in column D than you can filter column D on non blank. If the
rows you want to see contain the word Budget in column C you can apply a
custom filter with the condition Contains Budget.

 

[woodenschu]

Hi,

That particular limit is a formula length limit although scatter chart do
have low limits on the number of points, that's not the problem here.

If there is some common factor in one of the columns of your data area, you
can apply an AutoFilter to display only those rows you want.   Build your
chart from the filtered data.
Then with the chart selected choose Tools, Options, Chart, Plot visible
cells only.

You may need to add an extra column to your data area with a formula which
returns a unique entry to help you filter the data, but in general you can
find something that works.  For example, suppose the rows you don't want to
see are blank in column D than you can filter column D on non blank.  If the
rows you want to see contain the word Budget in column C you can apply a
custom filter with the condition Contains Budget.

--
Thanks,
Shane Devenshire











- Show quoted text -

Thanks, Shane - I'll see what I can do.

My real problem is that the cells are merged. So $G$1 through $G$4
are merged, $G$5 is solo, $G$6 through $G$10 are merged, etc. Even
though when you place the cursor on a merged cell, Excel thinks it's
only one cell ( i.e $G$1 ), if I CNTL-click to choose that cell, the
chart wizard uses $G$1:$G$4. Not sure why that is, but that's another
issue.

Thanks again,
Tim