Combine Bar with Single Line Chart

[brettmanning24]

Hello,
Please can someone help me with this question. I am trying to create
a bar chart with a single line chart.

The bars will display the totals for various years i.e.

2004 104
2005 107
2006 108
2007 110
2008 101

The line chart will show how 2008 is performing on a weekly basis i.e.

01/01/2008 = 100
07/01/2008 = 101
14/01/2008 = 102

The bar chart for 2008 will reflect this also, updating each time a
new week is added.

The Y Axis I will assume display a total i.e. 99-111 for example.

The X Axis needs to show the dates plotted for the line chart
01/01/2008 - 31/12/2008 (weekly) but how can I display the bar charts
on this axis?

If anyone can help I would be extremely grateful.

Brett

 

[brettmanning24]

Hello,
Please can someone help me with this question.  I am trying to create
a bar chart with a single line chart.

The bars will display the totals for various years i.e.

2004 104
2005 107
2006 108
2007 110
2008 101

The line chart will show how 2008 is performing on a weekly basis i.e.

01/01/2008 = 100
07/01/2008 = 101
14/01/2008 = 102

The bar chart for 2008 will reflect this also, updating each time a
new week is added.

The Y Axis I will assume display a total i.e. 99-111 for example.

The X Axis needs to show the dates plotted for the line chart
01/01/2008 - 31/12/2008 (weekly) but how can I display the bar charts
on this axis?

If anyone can help I would be extremely grateful.

Brett

Sorry it needs to be a column chart not bar!

 

[Jon Peltier]

So the line will overlay the column for 2008?

Make your column chart with the 2004 to 2008 data. Note that 2004 is the
first category and 2008 the fifth. The year 2008 spans 4.5 to 5.5 on the
category axis.

I converted the week ending dates to this 4.5 to 5.5 scale using this
formula:

=(A10-DATE(2007,12,31))/366+4.5

to convert for example the date in cell A10 (1/1/08) to a value of 3.5027. I
added this data to the chart, which added another column series. I selected
the new column series, and using Chart menu > Chart Type, converted the
series to an XY type (not a line type). This gave me my XY series on
secondary axes, which did not align with the column chart axes. I double
clicked the line series, and on the Axis tab, chose Primary. Now everything
is aligned and all that's needed it suitable formatting. I found it worked
best if I double clicked the column series, and on the Options tab, changed
the gap width to zero. I also changed the formatting so the columns had a
fill color but no border.

- Jon

 

[Jon Peltier]

I should add, change the Y axis scale so it starts at zero, otherwise
interpretation of the column values will be severely distorted. Excel
autoscales the axis to start at 94, which makes the 2008 value of 101 appear
to be less than half of 2007's 110.

- Jon

 

[brettmanning24]

I should add, change the Y axis scale so it starts at zero, otherwise
interpretation of the column values will be severely distorted. Excel
autoscales the axis to start at 94, which makes the 2008 value of 101 appear
to be less than half of 2007's 110.

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










- Show quoted text -

Thanks John.

I will give this a try.

 

[Del Cotter]

Thanks John.

I will give this a try.

I noticed that the annual averages are close to 100 for most years,
making them a little hard to tell apart if the y axis goes down to zero.

Jon is quite right that a zero origin y axis is essential for column
charts, and all visualisations that depend on a perception of area from
the origin. But have you considered abandoning the idea of bars for the
annual average, and using lines instead? If you format your data like
this...

Annual Weekly
31/12/2003
31/12/2003
31/12/2003 104
31/12/2004 104
31/12/2004
31/12/2004 107
31/12/2005 107
31/12/2005
31/12/2005 108
31/12/2006 108
31/12/2006
31/12/2006 110
31/12/2007 110
31/12/2007
31/12/2007 101
01/01/2008 101 100
07/01/2008 101 101
14/01/2008 101 102
31/12/2008 101
31/12/2008
31/12/2008

....then the chart wizard should automatically detect the dates and apply
a Time-scale x axis type, producing the correct graph type if you select
"Line Chart" in the wizard. This would let you have the y axis start
around 90, giving you a good separation of annual averages, without
misleading anyone (lines and symbols don't need a zero origin because
they don't depend on an area, only a difference in position).

And if you still want to see columns, you can quickly select the annual
series, choose "Chart Type.. Area", and columns will appear (they're
areas really, but they look like columns