force graph to axis of choice

i have a bar chart and when then added an xy scatter chart
i want the scatter chart to use the primary axis but it seems to insist on
using the 2ndary axis
i never saw a way to force the scatter to use the primary axis when creating
the scatter plots
i suppose i can change the scale of the 2ndary axis to match the primary but
then can't use
the 2ndary axis if i need it for different values
when i open the format data series for any of the xy scatter plots, the
place to choose the axis is greyed out

thanks for the super help again
i have been successful creating first the bars (but not floating bars), then
the vertical line or multiple vertical lines with the xy scatter
i looked at the bmslt example, that was kind of hard to follow, however i've
tried the examples on http://www.andypope.info/whatsnew.htm
he has 2 nice 'simple' examples which i've tried to use, what happens though
as soon as create a data series with cells i've populated
for the variable columns, for some reason adding that data series changes
the xy scatter plots, they just go away because their data series
cells get changed!!! bizarre and confusing, i'm gathering it might make a
difference in what order the different plots are added to the chart??
and you find that out by trying different combinations?? and that some
combinations just don't work like floating bars and xy scatter
and i guess you just find those incompatibles by trial and error.??? does it
make any difference if you create that charts with macros instead of
the charting interface???

"Jon Peltier" wrote in message
...
There are a few ways to try this. First I tried floating bars on the
primary axis, then a floating bar on the secondary axis to get the
transparent overlay. So far, so good. Then I tried adding a new series
for the vertical line, but when I tried to change that series to an XY
Scatter, I got the message that Excel can't make certain combinations of
charts. I guess Bar (primary) + Bar (secondary) + XY is out; Line won't
work in place of the XY, and Bar + Column works the same as Bar(pri) +
Bar(sec).

I tried a different approach for the column, using a variation of
Stephen Bullen's Variable Width Column Chart (http://bmsltd.co.uk). But
as noted above, I couldn't add an XY series to provide the vertical line.

Never one to give up easily, he he, I tried again, with the floating
bars in place. I added my XY series for the vertical line, and then I
added the variable width column. A little tweaking of the chart at that
point, and I got what I wanted.

The workbook's a mess, though, not ready for public consumption. I'll
have to clean it up before I let anyone see it. Maybe I'll be able to
post it in a day or two, or this description gets you where you need to
be.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

bbxrider wrote:
thanx for the replies
it is a bar chart not a column chart but now the problem has changed
that was the 2nd stage of creating a combo chart with bar, xy scatter,
and
transparent column, all 3 in the same plot area
i thought i needed the 2ndary axis 'saved' for the transparent column
but
evidently not
so i got the bars and xy scatter working ok, but whenever i try to add a
data series for
the transparent column, it blows out the data series or something for
the xy
scatter, bcause it goes away and i can' t seem to get it back
see, job1data.com/xcelChart1.htm for a better explanation

"Jon Peltier" wrote in message
...

Generally, when you put the XY Scatter series on the primary axis that
already houses a column series, the left edge of the chart (to the left
of the first category) is at X=0.5, the categories are at X=1, 2, 3,
etc., and the right edge of the chart is at N+0.5 for N categories (N
points in the column chart). The Y values can be made to coincide
perfectly onto the primary axis.

The OP said "bar" chart, which is the name many newcomers to Excel have
for column charts. If he really meant "bar" charts, then that's a
different story. The axes will be hard to make coincide: the bar chart's
X axis is atop the XY chart's Y axis, and vice versa. You cannot coerce
both series onto the primary axes, but you can uncheck the XY series' X
axis, and it will use the Y values of the bar chart; both of these axes
are value axes. The XY series' Y axis cannot be made to correspond to
the bar series' X axis, because one is a category axis, the other a
value axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Tushar Mehta wrote:


The nature of the Category(X) axis for a scatter chart and for a column
chart are very different. It is hard to make logical sense out of
placing the two chart types on the same axis. However, if you want to
do so, start with a XY Scatter chart, add the column chart data and
play with that setup. It yields some logically sensible results and
some bizarre results as one experiments with primary-only, secondary
y-, secondary x-, and moving the primary/secondary axes up/down, etc.

Let me include a hint or two that I left off of yesterday's hints.

It shouldn't matter whether you do it in VBA or manually through the
usual means.

For the transparent column, I used Stephen Bullen's approach, but I just
kept the data in the sheet instead of defined names. I set up 100 data
points for the columns, scaled between the axis min and the axis max. If
the midpoint of the column was between the lower and upper edges of the
band I wanted, I gave the bar the value needed to show the column;
outside of those limits, I made it not show. Then I had to transform the
X value for the XY series used to make the vertical line, so it matched
the 1 to 100 scale dictated by the columns.

I sounds like a lot of work, but I've done a bunch of these for various
clients lately, and I've got the technique down pat.

As I pointed out, it does matter in which order you add the series. When
the XY didn't work at all when added after the bar and column, I added
it before the column. When I added the column, the XY series jumped
along, so I knew I had to somehow adjust its X value.

It's not all trial and error, though. There's also smoke and mirrors
involved, plus a healthy dose of dumb luck.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

bbxrider wrote:

thanks for the super help again
i have been successful creating first the bars (but not floating bars), then
the vertical line or multiple vertical lines with the xy scatter
i looked at the bmslt example, that was kind of hard to follow, however i've
tried the examples on http://www.andypope.info/whatsnew.htm
he has 2 nice 'simple' examples which i've tried to use, what happens though
as soon as create a data series with cells i've populated
for the variable columns, for some reason adding that data series changes
the xy scatter plots, they just go away because their data series
cells get changed!!! bizarre and confusing, i'm gathering it might make a
difference in what order the different plots are added to the chart??
and you find that out by trying different combinations?? and that some
combinations just don't work like floating bars and xy scatter
and i guess you just find those incompatibles by trial and error.??? does it
make any difference if you create that charts with macros instead of
the charting interface???

"Jon Peltier" wrote in message
...

There are a few ways to try this. First I tried floating bars on the
primary axis, then a floating bar on the secondary axis to get the
transparent overlay. So far, so good. Then I tried adding a new series
for the vertical line, but when I tried to change that series to an XY
Scatter, I got the message that Excel can't make certain combinations of
charts. I guess Bar (primary) + Bar (secondary) + XY is out; Line won't
work in place of the XY, and Bar + Column works the same as Bar(pri) +
Bar(sec).

I tried a different approach for the column, using a variation of
Stephen Bullen's Variable Width Column Chart (http://bmsltd.co.uk). But
as noted above, I couldn't add an XY series to provide the vertical line.

Never one to give up easily, he he, I tried again, with the floating
bars in place. I added my XY series for the vertical line, and then I
added the variable width column. A little tweaking of the chart at that
point, and I got what I wanted.

The workbook's a mess, though, not ready for public consumption. I'll
have to clean it up before I let anyone see it. Maybe I'll be able to
post it in a day or two, or this description gets you where you need to

be.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

bbxrider wrote:

thanx for the replies
it is a bar chart not a column chart but now the problem has changed
that was the 2nd stage of creating a combo chart with bar, xy scatter,

and

transparent column, all 3 in the same plot area
i thought i needed the 2ndary axis 'saved' for the transparent column

but

evidently not
so i got the bars and xy scatter working ok, but whenever i try to add a
data series for
the transparent column, it blows out the data series or something for

the xy

scatter, bcause it goes away and i can' t seem to get it back
see, job1data.com/xcelChart1.htm for a better explanation

"Jon Peltier" wrote in message
...


Generally, when you put the XY Scatter series on the primary axis that
already houses a column series, the left edge of the chart (to the left
of the first category) is at X=0.5, the categories are at X=1, 2, 3,
etc., and the right edge of the chart is at N+0.5 for N categories (N
points in the column chart). The Y values can be made to coincide
perfectly onto the primary axis.

The OP said "bar" chart, which is the name many newcomers to Excel have
for column charts. If he really meant "bar" charts, then that's a
different story. The axes will be hard to make coincide: the bar chart's
X axis is atop the XY chart's Y axis, and vice versa. You cannot coerce
both series onto the primary axes, but you can uncheck the XY series' X
axis, and it will use the Y values of the bar chart; both of these axes
are value axes. The XY series' Y axis cannot be made to correspond to
the bar series' X axis, because one is a category axis, the other a
value axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Tushar Mehta wrote:



The nature of the Category(X) axis for a scatter chart and for a column
chart are very different. It is hard to make logical sense out of
placing the two chart types on the same axis. However, if you want to
do so, start with a XY Scatter chart, add the column chart data and
play with that setup. It yields some logically sensible results and
some bizarre results as one experiments with primary-only, secondary
y-, secondary x-, and moving the primary/secondary axes up/down, etc.