downside standard deviation

[Stephen]

Hi,

I have found a standard deviation function in Excel and
have used it in the column of numbers below. The average
being 30.97 and the standard deviation be 39.75.

But I need to know the "downside standard deviation,"
which is the the standard deviation of all of the numbers
below the average, that is below 30.97. I haven't found a
function in Excel for this. The only way I know to do it
is to copy all the numbers below 30.97 to a separate
column and do the standard deviation. Does anyone know a
simpler way? Here is the column:

45.80259
7.649806
-17.492966
38.2224
-0.039098
120.446022
17.183628
5.695066
39.07161
16.612884
17.517976
12.661228
17.925472
112.34281

Average 30.97
Standard deviation 39.75

Thanks a lot for any help,

Stephen

 

[AlfD]

Hi!

A quick way to do what you want:

Assume data is in A1:A14.
A16 has formula =average(A1:A14)

In B1 put =IF(A1<$A$16,A1,"") and copy down to B14
In B16 put =stdevp(B1:B14) if the data is the whole of the population
or =stdev(B1:B14) if you are sampling.

Al

 

[Dave Peterson]

Another way:

=STDEV(IF(A1:A14<AVERAGE(A1:A14),A1:A14))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

With your data in A1:A14, I got: 11.64946737.

 

[Jerry W. Lewis]

You can use an array formula and avoid using extra cells
=STDEV(IF(A1:A14<AVERAGE(A1:A14),A1:A14))
array entered (Ctrl-Shift-Enter)

Jerry

 

[Stephen]

Hi,

Thanks for your help. I guess I must be doing something
wrong.

When I cut and pasted the formula,

=STDEV(IF(F4:F17<AVERAGE(F4:F17),F4:F17))

I didn't get any brackets (even though I did the Shift-
Control-Enter instead of just Enter) and I got a #VALUE!
in the cell. I don't know that much about Excel so I am
not sure what I am doing wrong.

I thought maybe it was because I cut and pasted it. So I
typed in the formula. I got the brackets around it but in
the cell was now #N/A! When I hit Shift-Control-Enter a
couple more times, I lost the brackets and got the #value!

If anyone has any idea what I am doing wrong, I would
really appreciate knowing it.

Thanks again,

Stephen

 

[AlfD]

Hi!

Did you paste it into the formula bar? Then use C-S-
(Ctrl-shift-enter))?

Is your data in column F?

There aren't many other things that could be awry.

If all else fails: remember KiSS. You can still try the quick approac
I suggested ealier...!

Al

 

[thepcer]

Try this spreadsheet: http://investexcel.net/507/downside-deviation-excel/