PC Review


Reply
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
 
Reply With Quote
 
 
 
 
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

 
Reply With Quote
 
 
 
 
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)
 
Reply With Quote
 
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.


 
Reply With Quote
 
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.

>
>.
>

 
Reply With Quote
 
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

 
Reply With Quote
 
Member
thepcer's Avatar
Join Date: Jul 2010
Posts: 30
 
      22nd Mar 2012
Try this spreadsheet: http://investexcel.net/507/downside-deviation-excel/
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Downside deviation and Semi-Deviation Jason Microsoft Excel Worksheet Functions 2 24th Mar 2012 10:11 PM
Downside Deviation =?Utf-8?B?Slc=?= Microsoft Excel Misc 1 16th Nov 2011 09:56 PM
Calculating Sortino Ration (Downside Deviation) TimH Microsoft Excel Worksheet Functions 9 9th Aug 2011 01:11 PM
calculating standard deviation Bob Weiner Microsoft Excel Discussion 1 4th Feb 2004 07:25 PM
Add mean and Standard Deviation to Histogram 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.