I am trying to write a formula to calculate the Sortino Ratio defined as
(Expected (Observed) Return - Minimum Acceptable Return(MAR))/Downside
Deviation. Downside Deviation is the Standard Deviation of those returns
that are < MAR. I am familiar with STDEV function and IF function. Is there
a way to embed the IF function into the STDEV function so that it calculates
the STDEV only on the values in the range below a certain level?

=IF(STDEV(A1:A5)<your value,STDEV(A1:A5),"")


"TimH" wrote:

> I am trying to write a formula to calculate the Sortino Ratio defined as
> (Expected (Observed) Return - Minimum Acceptable Return(MAR))/Downside
> Deviation. Downside Deviation is the Standard Deviation of those returns
> that are < MAR. I am familiar with STDEV function and IF function. Is there
> a way to embed the IF function into the STDEV function so that it calculates
> the STDEV only on the values in the range below a certain level?
>

I believe this will return the STDEV if it is below a certain level. What
I'm looking for is to calulate the STDEV of only those values below a certain
level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
those values below 5% other values are excluded.

"Teethless mama" wrote:

> =IF(STDEV(A1:A5)<your value,STDEV(A1:A5),"")
>
>
> "TimH" wrote:
>
> > I am trying to write a formula to calculate the Sortino Ratio defined as
> > (Expected (Observed) Return - Minimum Acceptable Return(MAR))/Downside
> > Deviation. Downside Deviation is the Standard Deviation of those returns
> > that are < MAR. I am familiar with STDEV function and IF function. Is there
> > a way to embed the IF function into the STDEV function so that it calculates
> > the STDEV only on the values in the range below a certain level?
> >

TimH wrote:
> I believe this will return the STDEV if it is below a certain level. What
> I'm looking for is to calulate the STDEV of only those values below a certain
> level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
> the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
> those values below 5% other values are excluded.
>

With your data in A1:A6, array-enter the following:

=STDEV(IF(A1:A6<0.05,A1:A6,""))

Try, array-entered*:
=STDEV(IF(A1:A10<5%,A1:A10))

*Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"TimH" wrote:
> I believe this will return the STDEV if it is below a certain level. What
> I'm looking for is to calulate the STDEV of only those values below a certain
> level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
> the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
> those values below 5% other values are excluded.

That works! Thanks a lot.

"Max" wrote:

> Try, array-entered*:
> =STDEV(IF(A1:A10<5%,A1:A10))
>
> *Press CTRL+SHIFT+ENTER to confirm the formula
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:17,500 Files:358 Subscribers:55
> xdemechanik
> ---
> "TimH" wrote:
> > I believe this will return the STDEV if it is below a certain level. What
> > I'm looking for is to calulate the STDEV of only those values below a certain
> > level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
> > the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
> > those values below 5% other values are excluded.
>

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"TimH" <(E-Mail Removed)> wrote in message
news:B208CA23-0C8B-4CC5-BFAF-(E-Mail Removed)...
> That works! Thanks a lot.

Hi Tim

I look for the full Sortino ratio calculation, i read in.eggheadcafe.com that 2 years ago
you write this formula to EXCEL. i will be very grateful if you can help with the sortino ratio calculation in excel , the MAR that you use ,and what is a "good numbers" as result

Thank you
Moti


> On Tuesday, August 26, 2008 11:45 AM Tim wrote:

> I am trying to write a formula to calculate the Sortino Ratio defined as
> (Expected (Observed) Return - Minimum Acceptable Return(MAR))/Downside
> Deviation. Downside Deviation is the Standard Deviation of those returns
> that are < MAR. I am familiar with STDEV function and IF function. Is there
> a way to embed the IF function into the STDEV function so that it calculates
> the STDEV only on the values in the range below a certain level?


>> On Tuesday, August 26, 2008 12:00 PM Teethlessmam wrote:

>> =IF(STDEV(A1:A5)<your value,STDEV(A1:A5),"")
>>
>>
>> "TimH" wrote:


>>> On Tuesday, August 26, 2008 12:07 PM Tim wrote:

>>> I believe this will return the STDEV if it is below a certain level. What
>>> I'm looking for is to calulate the STDEV of only those values below a certain
>>> level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
>>> the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
>>> those values below 5% other values are excluded.
>>>
>>> "Teethless mama" wrote:


>>>> On Tuesday, August 26, 2008 12:16 PM Glenn wrote:

>>>> TimH wrote:
>>>>
>>>> With your data in A1:A6, array-enter the following:
>>>>
>>>> =STDEV(IF(A1:A6<0.05,A1:A6,""))


>>>>> On Tuesday, August 26, 2008 12:23 PM demechani wrote:

>>>>> Try, array-entered*:
>>>>> =STDEV(IF(A1:A10<5%,A1:A10))
>>>>>
>>>>> *Press CTRL+SHIFT+ENTER to confirm the formula
>>>>> --
>>>>> Max
>>>>> Singapore
>>>>> http://savefile.com/projects/236895
>>>>> Downloads:17,500 Files:358 Subscribers:55
>>>>> xdemechanik
>>>>> ---
>>>>> "TimH" wrote:


>>>>>> On Tuesday, August 26, 2008 12:48 PM Tim wrote:

>>>>>> That works! Thanks a lot.
>>>>>>
>>>>>> "Max" wrote:


>>>>>>> On Tuesday, August 26, 2008 11:46 PM Max wrote:

>>>>>>> Welcome
>>>>>>> --
>>>>>>> Max
>>>>>>> Singapore
>>>>>>> http://savefile.com/projects/236895
>>>>>>> Downloads:17,500 Files:358 Subscribers:55
>>>>>>> xdemechanik
>>>>>>> ---


>>>>>>> Submitted via EggHeadCafe
>>>>>>> ASP.NET- How to Raise Custom Events from a UserControl
>>>>>>> http://www.eggheadcafe.com/tutorials...ercontrol.aspx

[tyrian]

There's a spreadsheet to calculate the Sortino Ratio here.

Originally Posted by Moti wig

Hi Tim

I look for the full Sortino ratio calculation, i read in.eggheadcafe.com that 2 years ago
you write this formula to EXCEL. i will be very grateful if you can help with the sortino ratio calculation in excel , the MAR that you use ,and what is a "good numbers" as result

Thank you
Moti

[tyrian]

Examine the Sortino Ratio Excel spreadsheet here

Originally Posted by Moti wig

Hi Tim

I look for the full Sortino ratio calculation, i read in.eggheadcafe.com that 2 years ago
you write this formula to EXCEL. i will be very grateful if you can help with the sortino ratio calculation in excel , the MAR that you use ,and what is a "good numbers" as result

Thank you
Moti