Finding start and end points on a trendline

D

datadummy

Active Member
Joined
Mar 16, 2017
Messages
313
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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
shg said:
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]
Click to expand...

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?
Click to expand...

The trendline.

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

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
You must log in or register to reply here.

Similar threads

D
  • Question Question
dynamic range but moving
Replies
0
Views
156
dmheller
D
Z
  • Question Question
HOW TO MAKE SIMPLE FORMULA FOR TREND IDENTIFICATOR ?? (INCREASING/DECREASING)
Replies
3
Views
185
Kaper
Kaper
Rip1971
  • Question Question
more data points in x axis of graph
Replies
4
Views
380
jkpieterse
jkpieterse
D
  • Question Question
Plot one point on a graph based on 3 variables
Replies
1
Views
28
Kaper
Kaper
M
  • Question Question
Are Multiple Trendlines possible?
Replies
1
Views
440
mickeystanford_alumni
M

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

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