Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Hi,
I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
It sounds like you want a stacked column chart, rather than clustered
(consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Thanks,
Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Okay, I'm a little confused about how your data is setup and how you want
your chart to look. The xy scatter shouldn't be a problem, is your data mixed with missing data like below? Does each series have it's own x-axis date labels, or do they share the same column of dates? You can format your x-axis minimum and maximum to whatever dates you want. What do you mean by a column showing just day and month? You can format the x-axis on the number tab to show any format you want -- try typing in the custom format box dd-mm. 2004 2005 2006 01-01 02-01 03-01 12 04-01 05-01 12 18 06-01 14 21 28 07-01 16 32 08-01 18 09-01 40 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Thanks, Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Yes - I got dates /values like:
01-01-2004 12 23-03-2004 21 17-06-2004 19 25-10-2004 23 02-01-2005 34 04-05-2005 37 14-09-2005 35 03-02-2006 21 05-03-2006 45 01-04-2006 36 06-05-2006 37 29-05-2006 26 12-06-2006 23 etc. and want to show each year as a seperate serie in my chart where the x-axis are the day/month set and the y-axis are the values. Is it possible to have several x-axis - this might solve my problem? by a column just being a day/month value instead of day/month/year value I mean that even if I just enter the day and month in a cell that is formatted as "dd-mm" excel really handles the value in the cell as "dd-mm-yy" e.g. if I enter "21-03" excel handles the date as "21-03-2007". Thanks and regards Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I'm a little confused about how your data is setup and how you want your chart to look. The xy scatter shouldn't be a problem, is your data mixed with missing data like below? Does each series have it's own x-axis date labels, or do they share the same column of dates? You can format your x-axis minimum and maximum to whatever dates you want. What do you mean by a column showing just day and month? You can format the x-axis on the number tab to show any format you want -- try typing in the custom format box dd-mm. 2004 2005 2006 01-01 02-01 03-01 12 04-01 05-01 12 18 06-01 14 21 28 07-01 16 32 08-01 18 09-01 40 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Thanks, Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Okay, I think I see what you are trying to do. You want to ignore the year
so you that you can plot multiple years on the same scale. Unfortunately there isn't a way to ignore the year. Excel only understands dates to be calculated as the number of days from January 0, 1900 plus a fractional portion of a 24 hour day. This is called a serial date. You could make a copy of your data and set all the years to the same so they can be plotted on top of each other, and Excel can be smart about placing the dates in the right place along the x-axis. Create an XY scatter plot from just your 2004 data and date labels. Then, change the year on all your 2005 and 2006 date labels to be 2004 also. In Source Data dialog from the chart, add a series and select the "fake" 2004 label range for x-axis the the 2004 data range for the values. Add another series and do the same for the "fake" 2005 series x-axis labels range and data range for the values. Make sense? You can't have only a month and day in cell without the year, Excel will think that is just text. You also might be better off just creating 3 charts (one for each year) next to each other which would be more flexible and accurate. -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Yes - I got dates /values like: 01-01-2004 12 23-03-2004 21 17-06-2004 19 25-10-2004 23 02-01-2005 34 04-05-2005 37 14-09-2005 35 03-02-2006 21 05-03-2006 45 01-04-2006 36 06-05-2006 37 29-05-2006 26 12-06-2006 23 etc. and want to show each year as a seperate serie in my chart where the x-axis are the day/month set and the y-axis are the values. Is it possible to have several x-axis - this might solve my problem? by a column just being a day/month value instead of day/month/year value I mean that even if I just enter the day and month in a cell that is formatted as "dd-mm" excel really handles the value in the cell as "dd-mm-yy" e.g. if I enter "21-03" excel handles the date as "21-03-2007". Thanks and regards Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I'm a little confused about how your data is setup and how you want your chart to look. The xy scatter shouldn't be a problem, is your data mixed with missing data like below? Does each series have it's own x-axis date labels, or do they share the same column of dates? You can format your x-axis minimum and maximum to whatever dates you want. What do you mean by a column showing just day and month? You can format the x-axis on the number tab to show any format you want -- try typing in the custom format box dd-mm. 2004 2005 2006 01-01 02-01 03-01 12 04-01 05-01 12 18 06-01 14 21 28 07-01 16 32 08-01 18 09-01 40 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Thanks, Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Yes that is correctly understood. I was afraid that I had do things like you
suggested because if you hold the cursor over a point in the chart it will show a message with the year 2004 no matter what year the serie belongs to etc. The smart thing about having all the years in the same chart is that it is possible to see seasonal fluctuations etc. very easily Thanks for your valueable help! Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I think I see what you are trying to do. You want to ignore the year so you that you can plot multiple years on the same scale. Unfortunately there isn't a way to ignore the year. Excel only understands dates to be calculated as the number of days from January 0, 1900 plus a fractional portion of a 24 hour day. This is called a serial date. You could make a copy of your data and set all the years to the same so they can be plotted on top of each other, and Excel can be smart about placing the dates in the right place along the x-axis. Create an XY scatter plot from just your 2004 data and date labels. Then, change the year on all your 2005 and 2006 date labels to be 2004 also. In Source Data dialog from the chart, add a series and select the "fake" 2004 label range for x-axis the the 2004 data range for the values. Add another series and do the same for the "fake" 2005 series x-axis labels range and data range for the values. Make sense? You can't have only a month and day in cell without the year, Excel will think that is just text. You also might be better off just creating 3 charts (one for each year) next to each other which would be more flexible and accurate. -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Yes - I got dates /values like: 01-01-2004 12 23-03-2004 21 17-06-2004 19 25-10-2004 23 02-01-2005 34 04-05-2005 37 14-09-2005 35 03-02-2006 21 05-03-2006 45 01-04-2006 36 06-05-2006 37 29-05-2006 26 12-06-2006 23 etc. and want to show each year as a seperate serie in my chart where the x-axis are the day/month set and the y-axis are the values. Is it possible to have several x-axis - this might solve my problem? by a column just being a day/month value instead of day/month/year value I mean that even if I just enter the day and month in a cell that is formatted as "dd-mm" excel really handles the value in the cell as "dd-mm-yy" e.g. if I enter "21-03" excel handles the date as "21-03-2007". Thanks and regards Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I'm a little confused about how your data is setup and how you want your chart to look. The xy scatter shouldn't be a problem, is your data mixed with missing data like below? Does each series have it's own x-axis date labels, or do they share the same column of dates? You can format your x-axis minimum and maximum to whatever dates you want. What do you mean by a column showing just day and month? You can format the x-axis on the number tab to show any format you want -- try typing in the custom format box dd-mm. 2004 2005 2006 01-01 02-01 03-01 12 04-01 05-01 12 18 06-01 14 21 28 07-01 16 32 08-01 18 09-01 40 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Thanks, Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Hi Ole,
Another possible option is to insert a helper column next to your date column. Assuming your date in column A. Put =A1-DATE(YEAR(A1),1,0) in B1(helper) and drag down. This will give you the day of the year number for each date. You can then plot each year as a seperate series in your chart, hide your x axis values, and add dummy series to represent your months. Food for thought Martin "Ole" wrote in message ... Yes that is correctly understood. I was afraid that I had do things like you suggested because if you hold the cursor over a point in the chart it will show a message with the year 2004 no matter what year the serie belongs to etc. The smart thing about having all the years in the same chart is that it is possible to see seasonal fluctuations etc. very easily Thanks for your valueable help! Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I think I see what you are trying to do. You want to ignore the year so you that you can plot multiple years on the same scale. Unfortunately there isn't a way to ignore the year. Excel only understands dates to be calculated as the number of days from January 0, 1900 plus a fractional portion of a 24 hour day. This is called a serial date. You could make a copy of your data and set all the years to the same so they can be plotted on top of each other, and Excel can be smart about placing the dates in the right place along the x-axis. Create an XY scatter plot from just your 2004 data and date labels. Then, change the year on all your 2005 and 2006 date labels to be 2004 also. In Source Data dialog from the chart, add a series and select the "fake" 2004 label range for x-axis the the 2004 data range for the values. Add another series and do the same for the "fake" 2005 series x-axis labels range and data range for the values. Make sense? You can't have only a month and day in cell without the year, Excel will think that is just text. You also might be better off just creating 3 charts (one for each year) next to each other which would be more flexible and accurate. -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Yes - I got dates /values like: 01-01-2004 12 23-03-2004 21 17-06-2004 19 25-10-2004 23 02-01-2005 34 04-05-2005 37 14-09-2005 35 03-02-2006 21 05-03-2006 45 01-04-2006 36 06-05-2006 37 29-05-2006 26 12-06-2006 23 etc. and want to show each year as a seperate serie in my chart where the x-axis are the day/month set and the y-axis are the values. Is it possible to have several x-axis - this might solve my problem? by a column just being a day/month value instead of day/month/year value I mean that even if I just enter the day and month in a cell that is formatted as "dd-mm" excel really handles the value in the cell as "dd-mm-yy" e.g. if I enter "21-03" excel handles the date as "21-03-2007". Thanks and regards Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I'm a little confused about how your data is setup and how you want your chart to look. The xy scatter shouldn't be a problem, is your data mixed with missing data like below? Does each series have it's own x-axis date labels, or do they share the same column of dates? You can format your x-axis minimum and maximum to whatever dates you want. What do you mean by a column showing just day and month? You can format the x-axis on the number tab to show any format you want -- try typing in the custom format box dd-mm. 2004 2005 2006 01-01 02-01 03-01 12 04-01 05-01 12 18 06-01 14 21 28 07-01 16 32 08-01 18 09-01 40 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Thanks, Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Forgot to mention, format column B as general.
"MartinW" wrote in message ... Hi Ole, Another possible option is to insert a helper column next to your date column. Assuming your date in column A. Put =A1-DATE(YEAR(A1),1,0) in B1(helper) and drag down. This will give you the day of the year number for each date. You can then plot each year as a seperate series in your chart, hide your x axis values, and add dummy series to represent your months. Food for thought Martin "Ole" wrote in message ... Yes that is correctly understood. I was afraid that I had do things like you suggested because if you hold the cursor over a point in the chart it will show a message with the year 2004 no matter what year the serie belongs to etc. The smart thing about having all the years in the same chart is that it is possible to see seasonal fluctuations etc. very easily Thanks for your valueable help! Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I think I see what you are trying to do. You want to ignore the year so you that you can plot multiple years on the same scale. Unfortunately there isn't a way to ignore the year. Excel only understands dates to be calculated as the number of days from January 0, 1900 plus a fractional portion of a 24 hour day. This is called a serial date. You could make a copy of your data and set all the years to the same so they can be plotted on top of each other, and Excel can be smart about placing the dates in the right place along the x-axis. Create an XY scatter plot from just your 2004 data and date labels. Then, change the year on all your 2005 and 2006 date labels to be 2004 also. In Source Data dialog from the chart, add a series and select the "fake" 2004 label range for x-axis the the 2004 data range for the values. Add another series and do the same for the "fake" 2005 series x-axis labels range and data range for the values. Make sense? You can't have only a month and day in cell without the year, Excel will think that is just text. You also might be better off just creating 3 charts (one for each year) next to each other which would be more flexible and accurate. -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Yes - I got dates /values like: 01-01-2004 12 23-03-2004 21 17-06-2004 19 25-10-2004 23 02-01-2005 34 04-05-2005 37 14-09-2005 35 03-02-2006 21 05-03-2006 45 01-04-2006 36 06-05-2006 37 29-05-2006 26 12-06-2006 23 etc. and want to show each year as a seperate serie in my chart where the x-axis are the day/month set and the y-axis are the values. Is it possible to have several x-axis - this might solve my problem? by a column just being a day/month value instead of day/month/year value I mean that even if I just enter the day and month in a cell that is formatted as "dd-mm" excel really handles the value in the cell as "dd-mm-yy" e.g. if I enter "21-03" excel handles the date as "21-03-2007". Thanks and regards Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I'm a little confused about how your data is setup and how you want your chart to look. The xy scatter shouldn't be a problem, is your data mixed with missing data like below? Does each series have it's own x-axis date labels, or do they share the same column of dates? You can format your x-axis minimum and maximum to whatever dates you want. What do you mean by a column showing just day and month? You can format the x-axis on the number tab to show any format you want -- try typing in the custom format box dd-mm. 2004 2005 2006 01-01 02-01 03-01 12 04-01 05-01 12 18 06-01 14 21 28 07-01 16 32 08-01 18 09-01 40 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Thanks, Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Ole:
The type of chart you want is a Day Of year Chart. This links walks you through the steps: http://processtrends.com/pg_charts_D...comparison.htm Kelly koday*at*ProcessTrends*dot*com "Ole" wrote in message ... Yes that is correctly understood. I was afraid that I had do things like you suggested because if you hold the cursor over a point in the chart it will show a message with the year 2004 no matter what year the serie belongs to etc. The smart thing about having all the years in the same chart is that it is possible to see seasonal fluctuations etc. very easily Thanks for your valueable help! Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I think I see what you are trying to do. You want to ignore the year so you that you can plot multiple years on the same scale. Unfortunately there isn't a way to ignore the year. Excel only understands dates to be calculated as the number of days from January 0, 1900 plus a fractional portion of a 24 hour day. This is called a serial date. You could make a copy of your data and set all the years to the same so they can be plotted on top of each other, and Excel can be smart about placing the dates in the right place along the x-axis. Create an XY scatter plot from just your 2004 data and date labels. Then, change the year on all your 2005 and 2006 date labels to be 2004 also. In Source Data dialog from the chart, add a series and select the "fake" 2004 label range for x-axis the the 2004 data range for the values. Add another series and do the same for the "fake" 2005 series x-axis labels range and data range for the values. Make sense? You can't have only a month and day in cell without the year, Excel will think that is just text. You also might be better off just creating 3 charts (one for each year) next to each other which would be more flexible and accurate. -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Yes - I got dates /values like: 01-01-2004 12 23-03-2004 21 17-06-2004 19 25-10-2004 23 02-01-2005 34 04-05-2005 37 14-09-2005 35 03-02-2006 21 05-03-2006 45 01-04-2006 36 06-05-2006 37 29-05-2006 26 12-06-2006 23 etc. and want to show each year as a seperate serie in my chart where the x-axis are the day/month set and the y-axis are the values. Is it possible to have several x-axis - this might solve my problem? by a column just being a day/month value instead of day/month/year value I mean that even if I just enter the day and month in a cell that is formatted as "dd-mm" excel really handles the value in the cell as "dd-mm-yy" e.g. if I enter "21-03" excel handles the date as "21-03-2007". Thanks and regards Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I'm a little confused about how your data is setup and how you want your chart to look. The xy scatter shouldn't be a problem, is your data mixed with missing data like below? Does each series have it's own x-axis date labels, or do they share the same column of dates? You can format your x-axis minimum and maximum to whatever dates you want. What do you mean by a column showing just day and month? You can format the x-axis on the number tab to show any format you want -- try typing in the custom format box dd-mm. 2004 2005 2006 01-01 02-01 03-01 12 04-01 05-01 12 18 06-01 14 21 28 07-01 16 32 08-01 18 09-01 40 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Thanks, Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Year curves in charts?
Kelly -
Good example. I think the artificial date axis would work better with two dummy series: one with labels in the middle of each month, how yours currently works, and the second with tick marks between the months. At first the tick marks in the middle of each month threw me off. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Kelly O'Day" wrote in message ... Ole: The type of chart you want is a Day Of year Chart. This links walks you through the steps: http://processtrends.com/pg_charts_D...comparison.htm Kelly koday*at*ProcessTrends*dot*com "Ole" wrote in message ... Yes that is correctly understood. I was afraid that I had do things like you suggested because if you hold the cursor over a point in the chart it will show a message with the year 2004 no matter what year the serie belongs to etc. The smart thing about having all the years in the same chart is that it is possible to see seasonal fluctuations etc. very easily Thanks for your valueable help! Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I think I see what you are trying to do. You want to ignore the year so you that you can plot multiple years on the same scale. Unfortunately there isn't a way to ignore the year. Excel only understands dates to be calculated as the number of days from January 0, 1900 plus a fractional portion of a 24 hour day. This is called a serial date. You could make a copy of your data and set all the years to the same so they can be plotted on top of each other, and Excel can be smart about placing the dates in the right place along the x-axis. Create an XY scatter plot from just your 2004 data and date labels. Then, change the year on all your 2005 and 2006 date labels to be 2004 also. In Source Data dialog from the chart, add a series and select the "fake" 2004 label range for x-axis the the 2004 data range for the values. Add another series and do the same for the "fake" 2005 series x-axis labels range and data range for the values. Make sense? You can't have only a month and day in cell without the year, Excel will think that is just text. You also might be better off just creating 3 charts (one for each year) next to each other which would be more flexible and accurate. -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Yes - I got dates /values like: 01-01-2004 12 23-03-2004 21 17-06-2004 19 25-10-2004 23 02-01-2005 34 04-05-2005 37 14-09-2005 35 03-02-2006 21 05-03-2006 45 01-04-2006 36 06-05-2006 37 29-05-2006 26 12-06-2006 23 etc. and want to show each year as a seperate serie in my chart where the x-axis are the day/month set and the y-axis are the values. Is it possible to have several x-axis - this might solve my problem? by a column just being a day/month value instead of day/month/year value I mean that even if I just enter the day and month in a cell that is formatted as "dd-mm" excel really handles the value in the cell as "dd-mm-yy" e.g. if I enter "21-03" excel handles the date as "21-03-2007". Thanks and regards Ole "Christopher Scott [MSFT]" wrote in message ... Okay, I'm a little confused about how your data is setup and how you want your chart to look. The xy scatter shouldn't be a problem, is your data mixed with missing data like below? Does each series have it's own x-axis date labels, or do they share the same column of dates? You can format your x-axis minimum and maximum to whatever dates you want. What do you mean by a column showing just day and month? You can format the x-axis on the number tab to show any format you want -- try typing in the custom format box dd-mm. 2004 2005 2006 01-01 02-01 03-01 12 04-01 05-01 12 18 06-01 14 21 28 07-01 16 32 08-01 18 09-01 40 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Thanks, Unfortunately I don't have consecutive datasets so I'll need the x-y chart. Also I would rather like to use a line instead of bars. Is it possible to have a column like you show in your example with just day and month (I know it is possible to view it like that - but also behind?)? Thanks Ole "Christopher Scott [MSFT]" wrote in message ... It sounds like you want a stacked column chart, rather than clustered (consecutive)? So if your data is arranged like this below (just the first day of each month in the example), choose the stacked column or bar chart. If the axes seem swapped from what you want change the row/column orientation in the source data dialog. 2004 2005 2006 01-01 4 6 8 01-02 6 9 12 01-03 8 12 16 01-04 10 15 20 -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "Ole" wrote: Hi, I'm trying to create a chart were I can view the turnover for each year. I want e.g. year 2004 to be series 1, 2005 series 2 etc. and they should be shown above each other instead og in consucutive order. The x-axis is formatted as "dd-mm" from 01-01 to 31-12. How do I do this?? Thanks Ole |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I average 2 curves? y=10x+3 and y=11x-2 | Excel Worksheet Functions | |||
Bell Curves | Charts and Charting in Excel | |||
53 weeks in 2004 skews x-axis year increment on charts. | Charts and Charting in Excel | |||
Charts leaving off letters in months of year | Excel Discussion (Misc queries) | |||
combine 2 curves | Excel Worksheet Functions |