Adding an intersecting line on a chart

  • Thread starter Thread starter John Phillips
  • Start date Start date
J

John Phillips

Hi All

Sorry if this is simple but so am I !

I have a basic X-Y line chart with about 6 values plotted
to form a wiggly line. I've added a trendline to this to
smooth it. I now wish to add a vertical line at a certain
value on the x axis, draw this up to the trendline, then
trace a horizontal line across to the Y axis. It's the
sort of thing you'd do in secondary school but can I do it
in excel ?!?!? ...no

Any help would be appreciated.
Regards
John P
 
John -

Use LINEST to determine the slope and intersection of the trendline (or
the higher order terms if you've used that option). Put your X value
into a cell, use the LINEST coefficients to calculate Y in an adjacent
cell, then copy this 2-cell range, and use Paste Special from the Edit
menu to add the data to the chart as a new series (a single point
series). Now double click on the point to format it. On the Patterns
tab, use None for Line and Marker (unless you want to show the point),
on the X Error Bars tab, set a Minus error bar, with a Percentage of
100, and on the Y Error Bars tab, set another Minus error bar, again
with a Percentage of 100. The error bars form the lines that go back to
the axes.

- Jon
 
Back
Top