Identifying points on a trendline

datadummy

Active Member
Joined
Mar 16, 2017
Messages
312
Office Version
  1. 365
Platform
  1. Windows
I currently have a trend line with the following trend line equation, y=-0.0131x+580.53. I am trying to determine the start and end points of the trend line. I have 19 data points please let me know if sharing the data points would be helpful or are they relevant considering I am addressing the trend line?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try this macro. It calculates and displays the (x,y) start and end points of the trendline for the first chart on the active sheet.

Code:
Public Sub Trendline_Start_End_Points()

    Dim ch As Chart
    Dim ser As Series
    Dim xVals As Variant
    Dim trLine As Trendline
    Dim p1 As Long, p2 As Long
    Dim m As Double, b As Double
    
    Set ch = ActiveSheet.ChartObjects(1).Chart
    Set ser = ch.SeriesCollection(1)
    xVals = ser.XValues
    
    'Extract m and b values from linear trendline equation, y = mx + b
    
    For Each trLine In ser.Trendlines
        If trLine.DisplayEquation Then
            With trLine.DataLabel
                p2 = InStr(.Text, "x")
                b = Trim(Mid(.Text, p2 + 1))
                p1 = InStrRev(.Text, "=", p2)
                m = Trim(Mid(.Text, p1 + 1, p2 - p1 - 1))
            End With
        End If
    Next
    
    MsgBox "Trendline start point: x=" & xVals(1) & " y=" & m * xVals(1) + b & vbCrLf & _
           "Trendline end point: x=" & xVals(UBound(xVals)) & " y=" & m * xVals(UBound(xVals)) + b

End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top