Problem adding vertical marker line to line chart

[lbb]

I have a line chart with date values on the x axis. I want to add vertical
marker lines on the chart to show when certain events happened (which do not
correspond to dates for which there is data in the line chart). I did a
websearch and supposedly you can do this with something called the "series
method", so I tried that. It worked all right, except where my vertical bar
showed up on the chart was nowhere near the date that I'd assigned to it;
hence, not very useful. I tried the "error bar method" and the results were
even more ghastly and useless. Is there a method for doing this that
actually works?

Thanks,

 

[Andy Pope]

Hi,

Does this help?
http://www.andypope.info/ngs/ng47.htm

If not post back and include details of the article you used from your
websearch.

Cheers
Andy

 

[lbb]

Hi Andy,

There's one thing missing in your example -- that's how you get the proper
X values associated with the new data series. I have four of them and
they're just lining up with the major tick marks. I need them to show up
instead in locations that correspond to certain dates. Any ideas?

 

[Shane Devenshire]

Hi,

I didn't look at Andy's site but you can alway create a dummy series with
the dates you want to display the lines for.

For example, here is some sample data

A B C D
1/1/2009 8
1/2/2009 6
1/5/2009 5
1/6/2009 1
1/7/2009 9
1/8/2009 8
1/9/2009 2
1/12/2009 10
1/13/2009 3
1/14/2009 3
1/15/2009 9
1/16/2009 5
1/3/2009 10 0
1/10/2009 10 0

In the original data 1/3/2009 and 1/10/2009 are not part of the series, but
they are the dates at which you want a vertical line. The values ( 0 and 10
) represent the top and bottom of the line you want to include on your chart.

1. Plot all of the data as 3 lines.
2. Double-click the second or third series on the chart and on the Patterns
tab choose None for Line, and on the Options tab choose High-Low lines. You
would also set the other series line to None on its Patterns tab.
3. You can remove the series from the Legend if you want
4. I set the scale so that the Maximum is the height you want the line to
go to, here 10.
5. I also select the chart and choose Tools, Options, Chart tab, and choose
Interpolated as the Plot empty cells as.

 

[Andy Pope]

Hi,

You mean the part about adding new series, moving it to the secondary axis
and formatting the secondary x axis so specific dates correspond to dates
and not category tickmarks?

Cheers
Andy

 

[lbb]

Yeah -- I cleared up some of my confusion -- I thought it was a secondary Y
axis, didn't understand it was a secondary x axis. I got that part
straightened out, but it still isn't working. For example, one of the series
that I want to use to create a date marker has a value of 11000 (which is
where I want the point to show up as far as the Y value), category (x axis)
labels is the whole range for the regular chart (12 of these, one for each
month) and secondary label is the cell containing the date where I want the
bar to appear. It shows up, but it's nowhere near the date in the secondary
label. How exactly is Excel supposed to know where to place it?

I have to say, this all seems like one great big almighty kluge to do
something that ought to be a fairly standard function.

 

[Andy Pope]

I doubt it should be this complicated

If you want to send your file I will have a look see.

andy AT andypope DOT info

Cheers
Andy