Trying To Add a Line to an Excel chart using variable cell values

[Larry Campbell]

I'm using an XY Scatter chart and I want to draw a
straight line between 2 points which passes through a 3rd
point on the chart. The line represents the Security
Market Line (relating risk and return) with Standard
Deviation on the X axis and Portfolio Return Percentage on
the Y axis. One point is the 3 Month TBill. The point that
the line is passing through is an index point (e.g. S&P
500) and the 3rd point will be one that is calculated
based on the slope of relationship between the TBill point
and the index point. The 3rd point needs to be plotted on
either the top or bottom border of the graph, depending on
the numbers. I want the calculated point (3rd point) to be
the endpoint of the Security Market Line, but I don't want
it to show on the graph or in the legend. I'm using the
AddLine function which requires 4 arguments (i.e.
Beginning X, Beginning Y, Ending X, Ending Y). The
arguments are "point" values which appear to be relative
distances from the upper left corner of the chart. I need
to use and somehow set variables to the point values of
each of the arguments and then use those variable names as
the arguments because the values will vary with each
portfolio's risk and return.

If you need additional information, please let me know.

Thanks.

 

[Tushar Mehta]

I don't understand what it means to write "I don't want it to show on
the graph." Whether you add a line as a series or as an object, to
have any visual significance it has to show on the graph.

So, why not add it to the graph as a series? That way you can plot it
in terms of the x- and y- values themselves, and not try and figure out
plotting coordinates. In addition, the line will adjust itself if the
chart is reformatted.

And, to remove it from the legend, select the legend, pause, select the
entry for this new series, and press delete.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Peter Atherton]

Larry
I agree with Tushar about adding a new series. You do not
have to calculate every point just the first and last in
the next column. You can use these points to draw a line
between the two pionts.

Remember to group the line to the chart (select the line -
hold shift and select the chart. Choose Draw, Group.

Regards
Peter