Charts Having X Axis Scaled Using Calendar Weeks

  • Thread starter Thread starter Peter Müller
  • Start date Start date
P

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?
 
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 said:
Is your data weekly? Then make a line chart instead, with a category
(text label) axis, with one point per week.
Click to expand...

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.
 
Back
Top