If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Difficulty Aligning Series Data With Proper Dates
I ‘m building a column graph using Excel 2007 and I’m having difficulty
aligning a series of data with the proper dates. I have 2 series of data – Series 1: Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, etc). Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, Jun-10, etc). After creating each series, I select the appropriate dates (Series 1 – 2009 dates / Series 2 – 2010 dates). The first issue that I’m having is that when I select the second set of dates (for Series 2), it overrides the first – so the x-axis initially shows 2009 dates, but then changes to 2010. To get around this, I just reset the dates using the Format Axis option – resulting in all the 2009 and 2010 dates appearing on the x-axis.. Unfortunately, when I reset the dates in this manner, the 2009 columns of data are moved over to the 2010 dates – resulting in the Mar-09 column positioned next to the Mar-10 column. And the same applies to the other dates as well – Jun 09 is positioned next to Jun-10. What I want is the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4 quarterly columns for 2010 to be aligned with the 2010 dates. Any assistance you can provide would be greatly appreciated. Mike M. |
#2
|
|||
|
|||
Difficulty Aligning Series Data With Proper Dates
You can only use one set of dates. But it's possible to fake out Excel:
Plot Two Time Series With Different Dates http://peltiertech.com/WordPress/plo...fferent-dates/ Line charts and column charts work the same way. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Mike M. wrote: I ‘m building a column graph using Excel 2007 and I’m having difficulty aligning a series of data with the proper dates. I have 2 series of data – Series 1: Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, etc). Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, Jun-10, etc). After creating each series, I select the appropriate dates (Series 1 – 2009 dates / Series 2 – 2010 dates). The first issue that I’m having is that when I select the second set of dates (for Series 2), it overrides the first – so the x-axis initially shows 2009 dates, but then changes to 2010. To get around this, I just reset the dates using the Format Axis option – resulting in all the 2009 and 2010 dates appearing on the x-axis.. Unfortunately, when I reset the dates in this manner, the 2009 columns of data are moved over to the 2010 dates – resulting in the Mar-09 column positioned next to the Mar-10 column. And the same applies to the other dates as well – Jun 09 is positioned next to Jun-10. What I want is the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4 quarterly columns for 2010 to be aligned with the 2010 dates. Any assistance you can provide would be greatly appreciated. Mike M. |
#3
|
|||
|
|||
Difficulty Aligning Series Data With Proper Dates
Jon,
Thanks for your response. I read thru the suggestion and I will definitely try applying it to my graph. BTW, the way I got around this limitation was similar to your suggestion - by adding a secondary axis. I created a column chart for both series, then converted the second series (2010 data) to a line chart. I then added a secondary axis that aligned with the values of the second series. Its not as 'clean' as your suggestion, but with the legend clearly indicating the line graph represents 2010 data, the reader should be able to comprehend what they're seeing in the graph. Thanks again for your help. Mike M. "Jon Peltier" wrote: You can only use one set of dates. But it's possible to fake out Excel: Plot Two Time Series With Different Dates http://peltiertech.com/WordPress/plo...fferent-dates/ Line charts and column charts work the same way. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Mike M. wrote: I ‘m building a column graph using Excel 2007 and I’m having difficulty aligning a series of data with the proper dates. I have 2 series of data – Series 1: Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, etc). Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, Jun-10, etc). After creating each series, I select the appropriate dates (Series 1 – 2009 dates / Series 2 – 2010 dates). The first issue that I’m having is that when I select the second set of dates (for Series 2), it overrides the first – so the x-axis initially shows 2009 dates, but then changes to 2010. To get around this, I just reset the dates using the Format Axis option – resulting in all the 2009 and 2010 dates appearing on the x-axis.. Unfortunately, when I reset the dates in this manner, the 2009 columns of data are moved over to the 2010 dates – resulting in the Mar-09 column positioned next to the Mar-10 column. And the same applies to the other dates as well – Jun 09 is positioned next to Jun-10. What I want is the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4 quarterly columns for 2010 to be aligned with the 2010 dates. Any assistance you can provide would be greatly appreciated. Mike M. |
#4
|
|||
|
|||
Difficulty Aligning Series Data With Proper Dates
Many ways to skin that cat. You could also convert the chart to an XY
chart. If you want the nice date formatting of the X axis, you can keep the first series as a line chart series and convert any others to XY. (I know you talk of column charts, but that doesn't seem appropriate for a time series.) - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Mike M. wrote: Jon, Thanks for your response. I read thru the suggestion and I will definitely try applying it to my graph. BTW, the way I got around this limitation was similar to your suggestion - by adding a secondary axis. I created a column chart for both series, then converted the second series (2010 data) to a line chart. I then added a secondary axis that aligned with the values of the second series. Its not as 'clean' as your suggestion, but with the legend clearly indicating the line graph represents 2010 data, the reader should be able to comprehend what they're seeing in the graph. Thanks again for your help. Mike M. "Jon Peltier" wrote: You can only use one set of dates. But it's possible to fake out Excel: Plot Two Time Series With Different Dates http://peltiertech.com/WordPress/plo...fferent-dates/ Line charts and column charts work the same way. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Mike M. wrote: I ‘m building a column graph using Excel 2007 and I’m having difficulty aligning a series of data with the proper dates. I have 2 series of data – Series 1: Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, etc). Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, Jun-10, etc). After creating each series, I select the appropriate dates (Series 1 – 2009 dates / Series 2 – 2010 dates). The first issue that I’m having is that when I select the second set of dates (for Series 2), it overrides the first – so the x-axis initially shows 2009 dates, but then changes to 2010. To get around this, I just reset the dates using the Format Axis option – resulting in all the 2009 and 2010 dates appearing on the x-axis.. Unfortunately, when I reset the dates in this manner, the 2009 columns of data are moved over to the 2010 dates – resulting in the Mar-09 column positioned next to the Mar-10 column. And the same applies to the other dates as well – Jun 09 is positioned next to Jun-10. What I want is the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4 quarterly columns for 2010 to be aligned with the 2010 dates. Any assistance you can provide would be greatly appreciated. Mike M. |
Thread Tools | |
Display Modes | |
|
|