How to keep category and value axes the same

[Chris S]

Hi

I am currently trying to chart two series:

the first is series where the value are percentages and the categories
are number ranges (e.g. "1 to 5", "6 to 10", "11 to 15", etc...). i am
formatting this series as a column chart.

the second is a scatter series where the y value are always 100% and
the x values are numbers (e.g. 1.5, 2.2, 3.5, 4.1, etc...). i am
formatting this series as an scatter chart.

putting those together is easy: two series, one of which is an xy
scatter.

problem is one series is a category series and the other is a value
series.

so if i put both on the primary x-axis, both data sets sit side by
side in the chart and excel uses categories for the x-axis.

and if i put one series on the primary x-axis and the other on the
secondary x-axis, they do superimpose (which is what I am trying to
do), but the scales of the 2 x-axis is completely different (because
one is a category and the other a value axis).

Anyone know how I can solve this without using vba??? i.e. have a
value x-axis and a category x-axis with matching auto scales?

Many thanks and kudos to anywone with a solution here!

 

[Mike Middleton]

Chris -

For an example of combining a Column chart type and an XY (Scatter) chart
type, see the Histogram page at http://www.treeplan.com.

It may give you some ideas for solving your particular problem.

- Mike
http://www.MikeMiddleton.com

 

[Chris S]

Chris -

For an example of combining a Column chart type and an XY (Scatter) chart
type, see the Histogram page athttp://www.treeplan.com.

It may give you some ideas for solving your particular problem.

- Mikehttp://www.MikeMiddleton.com

Hi Mike - thanks for this. The page you pointed me to got me part of
the way to my solution.

The one thing I am still trying to do is to have primary and secondary
axes using the same intervals, max and min values automatically, as
opposed to the manula method in you page.

What I am trying to achieve is a histogram of frequency distribution
(column series), just as you have in your page, but with the second
series (the scatter series) ther to show average and standard
deviations of the data sets as vertical lines - i.e. the scales of
both series have to be same, and since the data sets changes
dynamically (i.e. intervals, max and min vary), I have to ba bale to
do this without manually setting x-scale values.

Do you know how to do this?

 

[Chris S]

Hi Mike - thanks for this. The page you pointed me to got me part of
the way to my solution.

The one thing I am still trying to do is to have primary and secondary
axes using the same intervals, max and min values automatically, as
opposed to the manula method in you page.

What I am trying to achieve is a histogram of frequency distribution
(column series), just as you have in your page, but with the second
series (the scatter series) ther to show average and standard
deviations of the data sets as vertical lines - i.e. the scales of
both series have to be same, and since the data sets changes
dynamically (i.e. intervals, max and min vary), I have to ba bale to
do this without manually setting x-scale values.

Do you know how to do this?

In fact, I am trying to do exactly your chart, but with 3, but with 3
additional vertical lines that represent the mean and two standard
deviations of the data set. And I am trying to make these lines appear
not in the middle of the bins, but in their exact positions (i.e. 5.1
would be a vertical line very close to 5 and very far from 10). Thanks
again!

 

[Del Cotter]

I am currently trying to chart two series:

problem is one series is a category series and the other is a value
series.

and if i put one series on the primary x-axis and the other on the
secondary x-axis, they do superimpose (which is what I am trying to
do), but the scales of the 2 x-axis is completely different (because
one is a category and the other a value axis).

What's your actual problem? As with so many classes of problems, there
may be more than one solution, but which of the solutions is best
depends on the precise nature of what you're trying to do.

If you have only one chart to do, then right-click on the Value x-axis,
select Format Axis.. Scale, then set the Minimum and Maximum to some
appropriate value that matches the bins.

Taking Mike's picture in http://www.treeplan.com/better.htm as an
example, if your bins are 0-5, 5-10, and so on until 45-50, then set the
value axis with minimum of 0 and maximum of 50. Take care that the bins
in your histogram actually all have equal width for this purpose-- in
this example they're all 5 units in width-- and your statistical
aggregate measures should line up nicely.

If you now say you have 900 charts to do and can't take time to do them
all manually, well, all I can say is that wasn't described in your
original question.

 

[Chris S]

What's your actual problem? As with so many classes of problems, there
may be more than one solution, but which of the solutions is best
depends on the precise nature of what you're trying to do.

If you have only one chart to do, then right-click on the Value x-axis,
select Format Axis.. Scale, then set the Minimum and Maximum to some
appropriate value that matches the bins.

Taking Mike's picture inhttp://www.treeplan.com/better.htmas an
example, if your bins are 0-5, 5-10, and so on until 45-50, then set the
value axis with minimum of 0 and maximum of 50. Take care that the bins
in your histogram actually all have equal width for this purpose-- in
this example they're all 5 units in width-- and your statistical
aggregate measures should line up nicely.

If you now say you have 900 charts to do and can't take time to do them
all manually, well, all I can say is that wasn't described in your
original question.

Thanks Del, but unfortunately my problem is the latter: many many
charts (i.e. I cannot manully change the scales, and therein lies my
problem). Any ideas?

 

[Del Cotter]

Thanks Del, but unfortunately my problem is the latter: many many
charts

It sounds like you need VBA, sorry. In another universe, I'd like to
think Excel would have a check box for "Make scale limits exact on
maximum and minimum". Then you could have dummy data to force the limits
of the x-axis to be a range of your choice. if you try to do that with
the present system, I believe there's a complicated algorithm that sets
the size of the scale bar.

Alternatively, have you considered building your histogram from an Area
Chart that uses a date scale, and your vertical lines from a Line Chart
that uses the same date scale? That feels like it could work. You just
have to be careful to create your histogram bars out of rectangular
areas, and have a large date range (a bit less than 3,000,000 days) for
precision.

The histogram bin labels would be a third line series, an invisible
dummy series carrying data labels.

 

[Chris S]

It sounds like you need VBA, sorry. In another universe, I'd like to
think Excel would have a check box for "Make scale limits exact on
maximum and minimum". Then you could have dummy data to force the limits
of the x-axis to be a range of your choice. if you try to do that with
the present system, I believe there's a complicated algorithm that sets
the size of the scale bar.

Alternatively, have you considered building your histogram from an Area
Chart that uses a date scale, and your vertical lines from a Line Chart
that uses the same date scale? That feels like it could work. You just
have to be careful to create your histogram bars out of rectangular
areas, and have a large date range (a bit less than 3,000,000 days) for
precision.

The histogram bin labels would be a third line series, an invisible
dummy series carrying data labels.

Thanks Del, I will try your date series suggestion, if not, VBA here I
come!

 

[Del Cotter]

Thanks Del, I will try your date series suggestion, if not, VBA here I
come!

Try this

http://www.branta.demon.co.uk/excel/histogram.xls

The vertical lines had to be scatter series, as they can't share the
same x-series as the histogram. But luckily that doesn't stop them using
the primary x axis, and as a bonus they are even positioned with decimal
accuracy, which I didn't know could happen with Time-scales. That means
it's much less necessary to mess with the Time-scale, which, in Line and
Area charts, only works for x values that are integers between zero and
a bit shy of three million (but see below about your bins)

(Why three million? It's 31 Dec 9999, in number of days from 1 Jan 1900.
If only Excel dates started from 1 Jan AD 1-- I had terrible trouble
with a chart of American History dates recently, because they were in
the 1700s and 1800s!)

I used a Line Chart series to make the histogram bars, so they're empty,
but if you want to fill them, it's easy to change the Chart Type of that
series to Area, so it looks exactly like columns of a bar chart.

I have a few questions:

Do you intend the bins to always be the same size, e.g. 5? If not, you
may still have to use VBA to alter the major unit in the scales.

Do you intend the bins to ever be non-integer or extend into negative
values? Both of these will be much harder to do with a Time-scale.

 

[Chris S]

Try this

http://www.branta.demon.co.uk/excel/histogram.xls

The vertical lines had to be scatter series, as they can't share the
same x-series as the histogram. But luckily that doesn't stop them using
the primary x axis, and as a bonus they are even positioned with decimal
accuracy, which I didn't know could happen with Time-scales. That means
it's much less necessary to mess with the Time-scale, which, in Line and
Area charts, only works for x values that are integers between zero and
a bit shy of three million (but see below about your bins)

(Why three million? It's 31 Dec 9999, in number of days from 1 Jan 1900.
If only Excel dates started from 1 Jan AD 1-- I had terrible trouble
with a chart of American History dates recently, because they were in
the 1700s and 1800s!)

I used a Line Chart series to make the histogram bars, so they're empty,
but if you want to fill them, it's easy to change the Chart Type of that
series to Area, so it looks exactly like columns of a bar chart.

I have a few questions:

Do you intend the bins to always be the same size, e.g. 5? If not, you
may still have to use VBA to alter the major unit in the scales.

Do you intend the bins to ever be non-integer or extend into negative
values? Both of these will be much harder to do with a Time-scale.

Del thanks again for this. I ended up using a hybrid of a 10 line vba
code snippet + an imporved and simplified "fake histogram with a
scatter chart" method that you suggested (i.e. ALL series are now
scatters, and therefore I avoid havinf differently scaled axes - the
VBA the goes and rescales ALL x-axes in my sheet so that the charted
data alwasy covers 100% of the x-axis).

Works great. Now, if I could only figure out how to shade those fake
columns...