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?

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

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
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Peter Müller wrote:
> 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 wrote:
> 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.

Jon Peltier wrote:
> 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.