Graphing Intercept Points

[rsiluch]

Hi there. What I am trying to do is insert intercepts into my line graph, so that my line graph is more easily readable. The problem I am facing is that my line graph is always changing based on the data that is input. Basically, without going into too much detail, I am creating a calculator that determines the best time to take your pension plan (CPP as I live in Canada). As such, the line graph is a representation of my actuarial table, which changes based on the data that is input. Is there any way I can insert intercept points that follow the changes in the graph to keep the points accurate?

Thanks so much for your help!!

 

[tonyyy]

rsiluch,

If you're comfortable with a vba solution the following might be one approach...

Sub InterceptPosition()

Dim tcChart As ChartObject
Dim kount As Long
Dim pt As Point

Set tcChart = Sheets(1).ChartObjects(1)
kount = tcChart.Chart.SeriesCollection(1).Points.Count

'''' set the desired point and apply a data label
tcChart.Chart.SeriesCollection(1).HasDataLabels = False ' note: this will clear all data labels
Set pt = tcChart.Chart.SeriesCollection(1).Points(kount - 365)
pt.ApplyDataLabels

'''' align the shape to the data label
With Sheets(1).Shapes(2)
    .Top = 415 ' arbitrary number from top of window
    .Left = pt.DataLabel.Left + tcChart.Left
End With

End Sub

Essentially the macro identifies a specific data label, then aligns a shape to that label. In this example, the .Points(kount - 365) is identifying a data point one year ago. And .Shapes(2) is a vertical line shape.

Good luck!

tonyyy

 

[rsiluch]

Thanks for your help! Sadly, I am not too familiar with VBA. I don't have the knowledge that I would need to set that up and run it on my spreadsheet as of yet.

Thanks anyways!