Thread Tools Rate Thread

# downside standard deviation

Stephen
Guest
Posts: n/a

 10th Jul 2004
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
Guest
Posts: n/a

 10th Jul 2004
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

--
Message posted from http://www.ExcelForum.com

Dave Peterson
Guest
Posts: n/a

 10th Jul 2004
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
(E-Mail Removed)

Jerry W. Lewis
Guest
Posts: n/a

 10th Jul 2004
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
Guest
Posts: n/a

 11th Jul 2004

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.

>
>.
>

AlfD
Guest
Posts: n/a

 11th Jul 2004
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

--
Message posted from http://www.ExcelForum.com

Member
Join Date: Jul 2010
Posts: 30

 22nd Mar 2012
Try this spreadsheet: http://investexcel.net/507/downside-deviation-excel/

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Jason Microsoft Excel Worksheet Functions 2 24th Mar 2012 10:11 PM =?Utf-8?B?Slc=?= Microsoft Excel Misc 1 16th Nov 2011 09:56 PM TimH Microsoft Excel Worksheet Functions 9 9th Aug 2011 01:11 PM Bob Weiner Microsoft Excel Discussion 1 4th Feb 2004 07:25 PM Anuta Microsoft Excel Discussion 2 1st Feb 2004 07:34 AM

Features

Advertising

Newsgroups

All times are GMT +1. The time now is 12:56 PM.