On Sat, 21 Jun 2014 04:32:55 -0700 (PDT), "(e-mail address removed)"
I am building tables of paired values. I have Excel worksheets
with the same matrix structure (i.e., row and column headings for > >
worksheet 1 are identical to those for worksheet 2, etc.). Each cell >
within a matrix holds a value unique to the pair of row/column > > >
headings. One of the tools I am using is a scatter diagram with one axis
representing values within the matrix from one worksheet (e.g., variable
1) and the other axis representing values within the matrix from a
second worksheet (e.g., variable 2). Relative to experts, I'm a pretty
basic Excel user. The only way I've been able to get a scatter diagram
representing all the cells is to copy and paste all of the contents of
the two matrices into two long, side-by-side, columns. I'm using Excel
2010. Is there a better way? Thanks.
You can build a scatter-gram with all the sheets named as data sources.
I dont know a better way than the following, but maybe Claus does.
1 start with your first sheet and build a scatter gram.
2 select one of the scatter-points in the graph
3 press cntrl-C and immediately cntrl-V
4 use Chart Tools..Design..Select Data
5 select Series2 and press Edit
6 change the name in each range from Sheet1 to Sheet2 (or whatever your
sheets are called)
7 repeat until you have the full set
A slightly nerdier way is
1 start with your first sheet and build a scatter gram.
2 select one of the scatter-points in the graph
3 click in the formula box, select the =SERIES... formula, and copy it
with control-C. Press x to get out of the formula box
4 with the scatter point still selected, press up-arrow until the
formula box is blank
5 click in the box and press contrl-V
6 edit the resulting =SERIES formula from using Sheet1 to Sheet2 (two
instances)
7 press tick to accept the edit and exit the formula box
8 goto 4 and keep editing in new names at point 6
The problem then is that each series has its own colour, but you can
correct that to make them all consistent.
Or you could consider it a bonus - to see which sheet gave all those
outliers.
HTH
JJ
