How to calculate std deviation

**mdfmcf** · 06-15-2012, 10:23 PM

I have 10 numbers. Cells B2 to B11 Two are negative (B2 and B8). How do I calculate std deviation with an excel formula

**jeffreybrown** · 06-15-2012, 10:27 PM

How about

=STDEV(B2:B11)

Do you want to ignore the negatives?

Array entered
=STDEV(IF(B2:B11>0,B2:B11))

**mdfmcf** · 06-15-2012, 10:29 PM

I don't want to ignore the negatives. Looking at fund returns over a 10 year period and have no clue how to calculate. Not sure what array entered means.

These are the 10 numbers

-20.86
31.64
12.62
6.08
15.72
5.59
-37.04
28.76
17.28
1.08

**jeffreybrown** · 06-15-2012, 10:32 PM

Well if you don't want to exclude the negative then you do not need the array formula.

Did you enter =STDEV(B2:B11)

**mdfmcf** · 06-15-2012, 10:38 PM

I did. The problem is that Excel shows it at 21.18. According to the fund data it shows it at 16.xx

Where am I messing up

**jeffreybrown** · 06-15-2012, 10:52 PM

Not sure I know anything different to do here, but is it possible the 16.xx is wrong? The fund data explicitly says standard deviation?

**mdfmcf** · 06-16-2012, 10:53 AM

I doubt it would be wrong since it is on various websites.

http://www.google.com/finance?cid=981977228692163
http://finance.yahoo.com/q/rk?s=VTSMX

See 10 yr std deviation number

**Cutter** · 06-16-2012, 11:00 AM

You'd have to be using the same data to arrive at that figure in order to match it. You'd need to know what figures they are using to arrive at that 16.44. I suspect it is the past 120 monthly performance figures as opposed to the last 10 annual performance figures.

**mdfmcf** · 06-16-2012, 11:44 AM

Originally Posted by Cutter

You'd have to be using the same data to arrive at that figure in order to match it. You'd need to know what figures they are using to arrive at that 16.44. I suspect it is the past 120 monthly performance figures as opposed to the last 10 annual performance figures.

I have a feeling you are correct. I am trying to find purely from 2002-2011 risk numbers for the fund, but all are through May 2012. Thanks so much.