Trendline with standard deviation

  • Thread starter Thread starter rfbolinger
  • Start date Start date
R

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

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

Thanks!
 
Back
Top