Microsoft Excel: Calculating Standard Deviations?

Question

In column (A) of an Excel spreadsheet, I have calculated a 150-day simple moving average (sma), updated daily, for each day over a period of 3000 days. In column (B) I want to calculate the corresponding values for 1 standard deviation from the 150-day sma. In Column (C) I want to calculate 2 standard deviations from the 150 sma and in Column D, I want to calculate 3 standard deviations from the 150 sma. What would be the formulas for Columns (B), (C) and (D)? Thank you.

Enginerd brings up a great point. What I am trying to do is find the value that would be 1,2 and 3 standard deviations above and below a 150 day simple moving average. How do I accomplish this in Excel?

Answer 1

the excel command for standard deviation is stdev(range) where the range is the list of numbers for which you want to calculate the std

you can easily mulitply 2* that value, 3* that value, or whatever

care should be taken to make sure you are getting the standard deviation you want

the standard deviation of the group of data that is each day's result, is different than the standard deviation of the moving average

Answer 2

If col A contains the averages and col B has the standard deviations [stdev(range)], then cells C1 through H1 would be:

=A1-B1
=A1+B1
=A1-2*B1
=A1+2*B1
=A1-3*B1
=A1+3*B1

And similarly for cells C2 through H2, C3 through H3, etc.

Hope this helps.

Answer 3

http://www.convert-me.com/en/