Charts Having X Axis Scaled Using Calendar Weeks

[Peter Müller]

I have an "XY (Scatter)" chart ( Scatter with data points connected by
smoothed lines). I want to use to display calendar weeks as labels for
the X axis. The problem there is that at the end of a year the number
jumps from 53 to 1. I read that one can do the job be adding a second X
axis but I have no further clue. I tried to add a second X axis but I
failed.

When I choose a Line chart (line with markers displayed at each data
value) and apply the following lines

ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R39C1"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R1C1:R39C1"
ActiveChart.SeriesCollection(3).XValues = "=Sheet1!R1C1:R39C1"
ActiveChart.SeriesCollection(4).XValues = "=Sheet1!R1C1:R39C1"

I can provide an X axis showing the calendar weeks but I can specify
precise X values for my data points. Due to the fact one can chose the
chart type and XValues to any Series object I assume that there is way
to create an X axis having the desired values.

Who can I provide such X axis?

 

[Herbert Seidenberg]

Excel 2007 Chart
Continuous X Y line plot by week at year end.
Secondary vertical and horizontal axis.
Manual example without code.
http://www.mediafire.com/file/2gzjxjcwutd/11_13_09.xlsx

 

[Jon Peltier]

Is your data weekly? Then make a line chart instead, with a category
(text label) axis, with one point per week.

Is your data not weekly? Hide the default X axis labels and tick marks,
and add another series. The X values will be the date you want to
correspond to each week number (e.g. Monday or Friday of each week), the
Y values will be zero (or whatever the Y minimum is), and in the next
column, list the week numbers. The series will be formatted using the
cross marker style (to simulate axis ticks) and no line.

Use one of these free Excel utilities to add the week numbers in the
third column as labels for this new series, and position them below the
points.

- Jon

 

[Peter Müller]

Excel 2007 Chart
Continuous X Y line plot by week at year end.
Secondary vertical and horizontal axis.
Manual example without code.
http://www.mediafire.com/file/2gzjxjcwutd/11_13_09.xlsx

Excel 2003 shall be supported.

 

[Peter Müller]

Is your data weekly? Then make a line chart instead, with a category
(text label) axis, with one point per week.

I have done it that way. For generating the data then mean for each week
is computed and for missing weeks the value is now interpolated.