Well, I don't know where I said "All is great." I merely pointed out that
properly arranging your data makes charting much easier.
As you said, it's not a matter of rows vs. columns. Your data is not rows or
column, it's a single row with alternating X and Y values. This is
particularly inefficient, but Mr Shorty and B R Ramachandran have both
presented better ways to arrange your data.
When specifying a single data range for multiple series, Excel does assume
that the first column (row) contains X values to be used for all series and
subsequent columns (rows) contain Y values for the different series. This is
actually valid behavior for a line or column chart, though not usually what
is intended for XY charts. But in this case you could add series one at a
time, which allows you to specify distinct X and Y ranges for each. Or you
could implement some kind of VBA solution which parses the data range for
you, bypassing the chart wizard. I have posted some examples he
http://peltiertech.com/Excel/ChartsH....html#xycharts
I also have a preliminary add-in which allows selection of data from a
number of nonstandard arrangements, but it's not ready for general
distribution. If you're interested in doing some testing, email me outside
of the forum, and I'll send the latest version.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
"spazminator" wrote in message
...
No, Jon. It's not easy, no matter what format I use. I tried Shorty's
point
set per row format and got the same bad results. If you think about it,
there's no difference between data in rows as data in columns. The entry
function still assumes that the first series' X values should be copied
into
every subsequent series. The scatterplot function is just broken and
should
be fixed and made more general and less specific to single data series
applications. I don't really appreciate your marketspeak "all is great"
comment. It helps not at all. What I would appreciate is a promise to
create
a new and better point graphing function.
--
lostinamazeoftwistyturnypassagesallthesame
"Jon Peltier" wrote:
This is a good example of how an appropriate data layout makes impossible
things easy. You say you need the data in this particular layout for
another
reason. Well, it's common that data may need different layouts within the
same workbook. One layout is suitable for on-screen viewing, another is
better for printed reports, a third is good for the data source for a
pivot
table, and a fourth might be needed as source data for your chart. Maybe
you
need five or six if there are alternative ways you want to display a
table
or chart.
Back in the days of multiplan or visicalc, you couldn't afford to "waste"
extra rows and columns on alternative depictions of the same data, but
now
we have gigabytes of disk space, and at least hundreds of megabytes of
ram.
Go for it, add a worksheet, or set aside another range on the same
worksheet. Copy the data, use Paste Special - Links to paste a linked
copy,
then drag these cells around into the proper orientation.
Now you can have your pretty table and pretty chart, and since their data
are linked, when the table changes, so will the chart.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
"spazminator" wrote in message
...
Exactly... can be done but not readily... reformat your data...
I think MS with just a little thought could add a new chart type that
would
be a lot more conducive to "pen plotting". Like allowing a
pen-up/pen-down
column or something. Also it seems to me that the default of assuming
the
X
column in an XY scatter plot is going to be the same for each new
series --
is just busted.
I hate that charting in general is so geared for accounting and
presentations and so limited for engineering or science uses.
Why isn't there a 3D XYZ scatterplot chart type for instance?
Why does one have to buy/load/install extensions for mathematical
analysis?
I will try your point-per-line idea though. It looks like the best
solution
so far. It's just that there are a lot of points and I need them in the
current format for other reasons on the sheet. Looks like I'll be
adding a
new worksheet for the chart.
Thanks for trying.
--
lostinamazeoftwistyturnypassagesallthesame
"MrShorty" wrote:
Others may have better ideas, but it seems to me your data aren't laid
out in a format conducive to what you want. I was able (using the
"source data" dialog I mentioned above) to get a plot that looks like
what you describe. I had to select each individual point separated by
commas within a given series. The final series definition looked like
=SERIES(Sheet1!R5C1,(Sheet1!R5C2,Sheet1!R5C4),(She et1!R5C3,Sheet1!R5C5),4).
That, of course, isn't conducive to readily adding points to a
series, or adding series to the chart. Can be done, but not readily.
If you lay your data out differently, it would be easier. Try a lay
out where the X data are in a column and the corresponding Y data are
in the adjacent column. Something like:
name A A B B C C D D (names doubled to emphasize column relationship)
axis X Y X Y X Y X Y
pt1 0 0 0 1 0 2 0 3
pt2 2 0 2 1 2 2 2 3
Then it's easy to add data points (simply add rows) and data series
(simply add pairs of columns going across and paste special) to your
chart.
--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile:
http://www.excelforum.com/member.php...o&userid=22181
View this thread:
http://www.excelforum.com/showthread...hreadid=493208