Year curves in charts?

  • Thread starter Thread starter Ole
  • Start date Start date
O

Ole

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
 
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,

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
 
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.
 
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
 
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.
 
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
 
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
 
Forgot to mention, format column B as general.



MartinW said:
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
Click to expand...
 
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
_______
 
Back
Top