Finding start and end points on a trendline

datadummy

Active Member
Joined
Mar 16, 2017
Messages
312
Office Version
  1. 365
Platform
  1. Windows
I have a graph with a trend line and am wondering how can I determine what the start and end points of the trend line are?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Click on the points at either end to see the coordinate values.

Or display the trendline equation to see the parameters and solve for y at xmin and xmax

Or use LINEST:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
m
[/td][td="bgcolor:#F3F3F3"]
b
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td="bgcolor:#E5E5E5"]
-1​
[/td][td="bgcolor:#E5E5E5"]
6​
[/td][td][/td][td][/td][td][/td][td]A2:B2: {=LINEST(B5:B7, A5:A7)}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td="bgcolor:#F3F3F3"]
x
[/td][td="bgcolor:#F3F3F3"]
y
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]
2​
[/td][td]
3​
[/td][td][/td][td="bgcolor:#F3F3F3"]
ybeg
[/td][td="bgcolor:#E5E5E5"]
4​
[/td][td]E5: =A5*$A$2 + $B$2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]
3​
[/td][td]
5​
[/td][td][/td][td]
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]
4​
[/td][td]
1​
[/td][td][/td][td="bgcolor:#F3F3F3"]
yend
[/td][td="bgcolor:#E5E5E5"]
2​
[/td][td]E7: =A7*$A$2 + $B$2[/td][/tr]
[/table]
 
Upvote 0
Click on the points at either end to see the coordinate values.

Or display the trendline equation to see the parameters and solve for y at xmin and xmax

Or use LINEST:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
A​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
B​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
C​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
D​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
E​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
1​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
m
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
b
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
-1​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2:B2: {=LINEST(B5:B7, A5:A7)}
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
4​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
x
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
5​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
ybeg
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
4​
[/TD]
[TD]E5: =A5*$A$2 + $B$2
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
6​
[/TD]
[TD]
3​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
7​
[/TD]
[TD]
4​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
yend
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
2​
[/TD]
[TD]E7: =A7*$A$2 + $B$2
[/TD]
[/TR]
</tbody>[/TABLE]

I've clicked on the trend line and no values are showing. So in your example I'm assuming the -1 and the 6 are your trend line values and if so how did you find them?
 
Upvote 0
The -1 is the slope and the 6 is the intercept. I used those values in the formulas in E5 and E7 to give the y coordinates of the endpoints; the x coordinates of those points are in A5 and A7.
 
Upvote 0
A couple questions, when you said "click on the points at either end to see the coordinate values" were you talking about the trend line or the data series line? Also how did you determine the slope and intercept? I have displayed the equation on the chart and am not sure what that data is telling me.
 
Upvote 0
when you said "click on the points at either end to see the coordinate values" were you talking about the trend line or the data series line?

The trendline.

I have displayed the equation on the chart and am not sure what that data is telling me.

The equation of a linear trendline is y=mx + b, where m is the slope and b is the point where the line intersects the y axis (the "y intercept")
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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