combination x-y scatter and column graph

[David]

Is it possible to produce and combination scatter / column graph with
*different* numbers of data points in each series?

It's kind of hard to explain what I mean, but imagine beng able to have one
of the series on a plane scatter graph drawn as rectangles from the x-axis to
the point and being able to specify the width of these rectangles as an
absolute number.

Whenever I try to set a series with many points in it to scatter and a
series wth only a few points in it to column, Excel produces garbage. It
seems to want to use a column graph sort of x-axis, not a scatter plot sort
of x-axis.

If you have octave (or maybe matlab) lying around, this is the sort of thing
I mean:

octave:1> x1=-3.5:0.1:3.5;
octave:2> y1=normal_pdf(x1);
octave:3> x2=-3:0.5:3;
octave:4> y2=normal_pdf(x2);
octave:5> [x2 y2]=bar(x2, y2);
octave:6> plot(x1, y1, x2, y2)


cheers

dc

 

[Jon Peltier]

Realize you have to add a series, then change its type. Start by using the
column data to create a column chart. Then add the X and Y data for the XY
series, which Excel at first adds as a column chart, and it usually looks
awful. Select the added series, and use Chart Type on the Chart menu to
change it to an XY type.

If you want variable width columns, there's more work involved, but it's
certainly possible:

http://peltiertech.com/Excel/ChartsHowTo/VariableWidthColumns.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[David]

Realize you have to add a series, then change its type. Start by using the
column data to create a column chart. Then add the X and Y data for the XY
series, which Excel at first adds as a column chart, and it usually looks
awful. Select the added series, and use Chart Type on the Chart menu to
change it to an XY type.

Hi Jon,

Thanks for your reply.

I ticked yes, this answers my question, but on closer inspection I see that
it doesn't quite do what I want.

Following your instructions gives me scatter data using a secondary x-axis
(which for some reason I hadn't so far been able to do - and this at least
gives better results).

In the example I gave in my original post, the column data uses a scale from
-3 to 3 and the scatter data used a scale from -3.5 to 3.5.

With some fiddling, I can add an extra blank category either side of the
column data so it also goes from -3.5 to 3.5 and then format the secondary x
axis so that -t goes from -3.75 to 3.75, which gives me the same scale on the
two axes (since the column widths are 0.5). I can jump through more hoops
(turn off tick marks and tick labels) to hide most elements of this secondary
axis, producing a reasonable graph.

It is not straight forward to get the two scales to line up because one is
categorical and one is continuous. I really want the column data to be on a
continuous sort of axis at the same scale as the scatter data.

But maybe this is as good as Excel can do.

cheers,

dc

 

[Mike Middleton]

David -

Some of the ideas described in my Better Histogram article at
www.treeplan.com may be useful.

- Mike

 

[Jon Peltier]

David -

You can share axes.

Use this data to make a column chart:

Column
-3 4
-2 5
-1 6
0 7
1 8
2 9
3 10

Add this (manufactured) data to the chart as a new series, then select the
new series and change it to an XY type:

XY

-2.93636 4

3.079892 5

-3.25493 6

-1.05927 7

-3.04437 8

2.857509 9

1.644909 10



It uses -4 to +4 as its X axis range. If you go to Chart Options on the
Chart menu, Axes tab, and uncheck the secondary X axis, some of the XY
points still appear, and they appear at continuously varying horizontal
positions, but they are offset from where you want them.


If you use a category axis for an XY series, it assumes the category values
are 1 for the first category, 2 for the second, etc. In the column chart,
the effective X axis values are -3.5 to +3.5, since the Y axis is moved to
the left of the first category ("between categories" in the dialog). This
means your X values will think the axis ranges from 0.5 to 7.5.

Instead of the last set of data, use this set for your XY series, where the
X values are related to those above by adding 4:

XY

1.063642 4

7.079892 5

0.745069 6

2.940726 7

0.955635 8

6.857509 9

5.644909 10



Add the series, convert to XY, then go to Chart Options from the Chart menu,
Axes tab, and uncheck the secondary X and Y axes.


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jon Peltier]

You can even format the XY series so it's plotted on the primary axes with
the columns (double click on the series, then use the Axis tab). You still
need to add 4 to the values to make them plot properly.

Note that the data ranges I showed in my previous post should not have blank
lines. I've just started using Outlook Express for newsgroups, and I've
learned that the formatting options you set for OE are not very robust, and
you can't readily import tables from Excel or Word without strange stuff
like this happening.

I would have expected OE to interact better with Excel and Word than
Netscape does.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______