If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating Sortino Ration (Downside Deviation)
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? |
#2
|
|||
|
|||
Calculating Sortino Ration (Downside Deviation)
=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? |
#3
|
|||
|
|||
Calculating Sortino Ration (Downside Deviation)
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? |
#4
|
|||
|
|||
Calculating Sortino Ration (Downside Deviation)
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:A60.05,A1:A6,"")) |
#5
|
|||
|
|||
Calculating Sortino Ration (Downside Deviation)
Try, array-entered*:
=STDEV(IF(A1:A105%,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. |
#6
|
|||
|
|||
Calculating Sortino Ration (Downside Deviation)
That works! Thanks a lot.
"Max" wrote: Try, array-entered*: =STDEV(IF(A1:A105%,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. |
#7
|
|||
|
|||
Calculating Sortino Ration (Downside Deviation)
Welcome
-- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "TimH" wrote in message ... That works! Thanks a lot. |
Thread Tools | |
Display Modes | |
|
|