Trendlines

[Garf]

I am doing a basic spreadsheet that has a linear trendline applied, how do i
show the end value of the forecasted trendline on the spreadsheet

 

[Jim Thomlinson]

One way would be to use the Slope and Intercept formula. You can get this
equation by right clicking your trend line and selecting Format TrendLine...
-> Options tab -> check Show Equation on Chart.

Here is a sample I did... In Cells A1:B13
Month Sales
Jan 500
Feb 400
Mar 600
Apr 400
May 500
Jun 400
Jul 300
Aug 400
Sep 500
Oct 400
Nov 300
Dec 200

Now use the Formula Slope and Intercept...
=SLOPE( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12})
-19.23
=INTERCEPT( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12})
=533.33

To get the end of the trend line do something like this...

=INTERCEPT( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12}) + SLOPE( B2:B13,
{1,2,3,4,5,6,7,8,9,10,11,12}) * 12
302.56

 

[Luke M]

Look in XL help file about LINEST function. This will give you the slope and
y-itnercept of your trendline, which you can then use to calculate any x or y
value if you know the corresponding value.

 

[Garf]

One way would be to use the Slope and Intercept formula. You can get this
equation by right clicking your trend line and selecting Format TrendLine...
-> Options tab -> check Show Equation on Chart.

Here is a sample I did... In Cells A1:B13
Month Sales
Jan 500
Feb 400
Mar 600
Apr 400
May 500
Jun 400
Jul 300
Aug 400
Sep 500
Oct 400
Nov 300
Dec 200

Now use the Formula Slope and Intercept...
=SLOPE( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12})
-19.23
=INTERCEPT( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12})
=533.33

To get the end of the trend line do something like this...

=INTERCEPT( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12}) + SLOPE( B2:B13,
{1,2,3,4,5,6,7,8,9,10,11,12}) * 12
302.56

 

[Garf]

Thanks Jim

Will give it a go

Regards

Garf

One way would be to use the Slope and Intercept formula. You can get this
equation by right clicking your trend line and selecting Format TrendLine...
-> Options tab -> check Show Equation on Chart.

Here is a sample I did... In Cells A1:B13
Month Sales
Jan 500
Feb 400
Mar 600
Apr 400
May 500
Jun 400
Jul 300
Aug 400
Sep 500
Oct 400
Nov 300
Dec 200

Now use the Formula Slope and Intercept...
=SLOPE( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12})
-19.23
=INTERCEPT( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12})
=533.33

To get the end of the trend line do something like this...

=INTERCEPT( B2:B13, {1,2,3,4,5,6,7,8,9,10,11,12}) + SLOPE( B2:B13,
{1,2,3,4,5,6,7,8,9,10,11,12}) * 12
302.56

 

[Garf]

Cheers Luke

Will give it a go

Regards

Garf

Look in XL help file about LINEST function. This will give you the slope and
y-itnercept of your trendline, which you can then use to calculate any x or y
value if you know the corresponding value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Garf]

Hi Jim

You wouldn't happen to know the equation for a Logarithmic trendline would
you, it seems to fit my dataset better