Calculating the Intersection in a Line Chart

[Tim Dobyns]

Any Ideas on calculating the intersection point of two lines on a line chart
and then attaching a data lable or text box?

[jjohnson]

You can do this manually in Excel or automatically through a VBA macro.

In Excel, click once on the intersection of the two lines to activate a
series. Click a second time to select the intersecting point. Right-click on
the point and choose ‘Format Data Point’. In the ‘Data Lables’ tab, choose
‘Show Value’. Click OK. Click once on the label to activate it. Click a
second time to edit the label.

If your data contains a perfect intersection*, you could use a VBA macro to
compare each point to see which x or y values match. To attach a label, you
can set a point’s ‘HasDataLabel’ property to ‘True’ and its
‘DataLabel.Characters.Text’ property to whatever you want. See the AddLabel
sub routine below.

* Otherwise, you need to calculate the intersection and find the closest
datapoint in your dataset. The formula to calculate the x-y coordinates of
the intersection of two lines is: x = (b(1) - b(2)) / (m(2) - m(1)), y =
m(1)x + b(1) where y(1) = m(1)x(1) + b(1) and y(2) = m(2)x(2) + b(2) wher y
is the independent variable, m is SLOPE, x is the dependent variable, and b
is INTERCEPT.

This routine is an example of how the automated method might be done. Copy
this code into a module, click on a chart, and run from Tools: Modules.

Sub AddLabel()
Dim chrChart As Chart, srsSeries1, srsSeries2 As Series, _
varValues1, varValues2 As Variant, intPoints As Integer
Set chrChart = ActiveChart
Set srsSeries1 = chrChart.SeriesCollection(1)
Set srsSeries2 = chrChart.SeriesCollection(2)

varValues1 = srsSeries1.Values
varValues2 = srsSeries2.Values
intPoints = UBound(varValues1)

For i = 1 To intPoints
If varValues1(i) = varValues2(i) Then
srsSeries1.Points(i).HasDataLabel = True
srsSeries1.Points(i).DataLabel.Characters.Text = “myLabel”
Exit Sub
End If
Next
End Sub

Regards,
Jeremy

Tim Dobyns wrote in message ...

[Tim Dobyns]

Thanks..This will do the trick.

Tim
jjohnson <jjoh...@anet.com> wrote in message
news:#q2S3aHA$GA.249@cppssbbsa05...