-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Trendline data points
Locating points on a trend line
I have the following chart and am trying to determine what the end points are for the trend line. I think I was able to calculate the far left value by using the TREND function which returned 2.04, but am unable to determine what the far right value for the trend line is. Any ideas?
I have the following chart and am trying to determine what the end points are for the trend line. I think I was able to calculate the far left value by using the TREND function which returned 2.04, but am unable to determine what the far right value for the trend line is. Any ideas?
Last edited by a moderator:
Upvote
0
Jon Peltier
MrExcel MVP
- Joined
- May 14, 2003
- Messages
- 5,320
- Office Version
- 365
- Platform
- Windows
- MacOS
Re: Locating points on a trend line
Use SLOPE(Yrange,Xrange) and INTERCEPT(Yrange,Xrange) to get the slope and intercept of the fitted line in the worksheet. Use MIN(Xrange) and MAX(Xrange) to get the minimum and maximum X values, then find the respective Y values using YatXmin=SLOPE*Xmin+INTERCEPT and YatXmax=SLOPE*Xmax+INTERCEPT.
Formulas:
F2: =SLOPE(C3:C14,B3:B14)
F3: =INTERCEPT(C3:C14,B3:B14)
E7: =MIN(B3:B14)
E8: =MAX(B3:B14)
F7: =E7*$F$2+$F$3
F8: =E8*$F$2+$F$3
Use SLOPE(Yrange,Xrange) and INTERCEPT(Yrange,Xrange) to get the slope and intercept of the fitted line in the worksheet. Use MIN(Xrange) and MAX(Xrange) to get the minimum and maximum X values, then find the respective Y values using YatXmin=SLOPE*Xmin+INTERCEPT and YatXmax=SLOPE*Xmax+INTERCEPT.
Formulas:
F2: =SLOPE(C3:C14,B3:B14)
F3: =INTERCEPT(C3:C14,B3:B14)
E7: =MIN(B3:B14)
E8: =MAX(B3:B14)
F7: =E7*$F$2+$F$3
F8: =E8*$F$2+$F$3
Upvote
0
