downside standard deviation

Discussion in 'Microsoft Excel Misc' started by Stephen, Jul 10, 2004.

1. StephenGuest

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

Stephen, Jul 10, 2004

2. AlfDGuest

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

--

AlfD, Jul 10, 2004

3. Dave PetersonGuest

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.

Stephen wrote:
>
> 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

--

Dave Peterson

Dave Peterson, Jul 10, 2004
4. Jerry W. LewisGuest

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

AlfD < wrote:

> 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.

Jerry W. Lewis, Jul 10, 2004
5. StephenGuest

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

>-----Original Message-----
>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
>
>AlfD < wrote:
>
>> 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.

>
>.
>

Stephen, Jul 11, 2004
6. AlfDGuest

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

--

AlfD, Jul 11, 2004

Joined:
Jul 26, 2010