Add vertical lines to a line chart (already has 2 Y axis)

[PaulW]

Hi, I have a report I update each month. And while most of the information is
based on numbers one of the series is a percentage, so has it own axis.

I've set up the spreadsheet so that each week when data is added then
another part of the spreadsheet shows it, this part of the spreadsheet only
shows the last 28 weeks, so I have no need to change the cells the graph is
looking at (since it always shows the last 28 weeks)

The problem is that I need to have vertical lines to show different months.
The closest i've managed is to put a seconardy X axis with gridlines for
every month. But this unfortunatly starts where the Y Axis crosses, which is
the 15th May, so this shows the 15th of each month, rather than statically
the first/last of each month.

I've had a look on this forum, and found
http://peltiertech.com/Excel/Charts/AddLine.html
But I couldn't get it to work. I followed it a few times and I must be
getting something wrong because when I paste the information I get a zig zag
line on the left side, it doesn't correspond to the x axis. And when I change
it to scatter type it disappears altogether, and moves the graph so the left
Y Axis goes down to -50, when there are no minus figures on the graph.

Can anyone help me? drawing lines onto the graph and moving them isn't too
productive...

 

[Jon Peltier]

What kind of chart is it to begin with (i.e., what kind of X axis does it
have)? Have you adjusted the X values of the added series to align where you
want on the original X axis?

- Jon

 

[PaulW]

Its a line graph (with markers at each point)
The X axis are dates, starting at 15/05/06 (currently) and ending at
20/11/06 and each point (each line of data) is every week. So 15/05/06,
22/05/06, 29/05/06 etc.
The Primary Y axis is from 0 to 300, each major line is each 50. And the
Secondary Y axis is from 0% to 12%.

For my final attempt I set the X Axis for the added series as exactly the
same, with all the points as 0% apart from the ones closest to the beginning
of the month (29/05/06, 26/06/06, 31/07/06). But my initial attempt only
included the dates 01/06/06, 01/07/06, 01/08/06, 01/09/06 and 01/10/06. My
second attempt included these dates twice, one with a 0, and one with a 1
next to it.

The X axis has a base unit of Day(s) with a Major unit of 7 Day(s) and a
minor unit of 1 Day(s). It crosses the Y Axis at 15/05/06, and the option
"Value (Y) axis crosses between dates" is ticked.

The information for the chart is currently in Chart Data!A39:I67. Column B
is not included in the chart, and column I is the one with the percentage (on
the secondary Y Axis.) All these cells are formulas, and work from the sheet
"Data" which is a running list and looks for the MAX() Date, and works
backwards from there. So the information in A39:I67 won't need to be updated
manually.

The sheet currently doesn't have a secondary X Axis, but did at one point
which I removed due to it not being nessessory.

Hopefully this is enough information to help me, thanks for trying.

 

[Jon Peltier]

Have you changed the added series to XY? If it is still a Line type, it will
not use its own X values, but those X values already in use, so it may not
be appearing where you expect.

If the XY series has values of 0 and 1, and you selected a line without
markers, it may be utterly lost in the 0 to 300 primary Y axis scale. ) to 1
is for when you use a secondary Y axis, scaled 0 to 1, for this series only.
If you are using axes for other series, you have to use values which fit
these other axes.

- Jon