Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combine Bar with Single Line Chart
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combine Bar with Single Line Chart
On 10 Jan, 10:06, wrote:
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! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combine Bar with Single Line Chart
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, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ wrote in message ... 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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combine Bar with Single Line Chart
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 _______ "Jon Peltier" wrote in message ... 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, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ wrote in message ... 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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combine Bar with Single Line Chart
On 10 Jan, 12:54, "Jon Peltier"
wrote: 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 _______ "Jon Peltier" wrote in message ... 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, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ... 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- Hide quoted text - - Show quoted text - Thanks John. I will give this a try. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to combine stock and line to one chart | Excel Discussion (Misc queries) | |||
Combine non-matching dates in single chart | Charts and Charting in Excel | |||
how to combine a vertical line chart into a bar chart? | Charts and Charting in Excel | |||
Combine candle and line in a single chart | Charts and Charting in Excel | |||
Combine candle and line in a single chart | Charts and Charting in Excel |