Trendline with standard deviation

[rfbolinger]

I have a historical stock price chart into which I've inserted a
trendline; I'd like to now show a line graph with one standard
deviation above the trendline and one below; ideas please?

 

[MartinW]

Hi Rf,

Say your x data is in A1:A10 and your y data in B1:B10

Put this in C1
=TREND(B1:B10,A1:A10,A1)+STDEV(B1:B10)
(note the A1 is for the *first* point in your series)

and this in C2
=TREND(B1:B10,A1:A10,A10)+STDEV(B1:B10)
(note the A10 is for the *last* point in your series)

Now go to source data and add a series with
X-Values =(Sheet1!$A$1,Sheet1!$A$10)
Y-Values =Sheet1!$C$1:$C$2

You can then set the series to show a line but no marker
or add a trendline if you wish.

HTH
Martin

 

[Mike Middleton]

And, if you would prefer to use the "standard error of estimate,"
approximately equal to the standard deviation of the residuals, use the
STEYX worksheet function instead of STDEV.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[rfbolinger]

Hi Rf,

Say your x data is in A1:A10 and your y data in B1:B10

Put this in C1
=TREND(B1:B10,A1:A10,A1)+STDEV(B1:B10)
(note the A1 is for the *first* point in your series)

and this in C2
=TREND(B1:B10,A1:A10,A10)+STDEV(B1:B10)
(note the A10 is for the *last* point in your series)

Now go to source data and add a series with
X-Values =(Sheet1!$A$1,Sheet1!$A$10)
Y-Values =Sheet1!$C$1:$C$2

You can then set the series to show a line but no marker
or add atrendlineif you wish.

HTH
Martin






- Show quoted text -

Cool!!!!!

 

[rfbolinger]

And, if you would prefer to use the "standard error of estimate,"
approximately equal to the standard deviation of the residuals, use the
STEYX worksheet function instead of STDEV.

- Mike Middletonhttp://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel











- Show quoted text -

Thanks!