Help Translating Formula to VBA Code

[ttbuson]

Can someone please help me translate the formula:

{=(SUMSQ(IF(C3:C14-G2>0,0,C3:C14-G2))/COUNT(C3:C14))^(1/2)}

into a VBA user defined function? So far, I have:

(where C3:C14 is the MonthRet and G2 is the MAR in the above formula)

Function DownsideDev(MonthRet As Range, MAR As Double) As Double
' Calculates Downside Deviation below a Minimum Acceptable Return.
' {=(SUMSQ(IF(C3:C14-G2>0,0,C3:C14-G2))/COUNT(C3:C14))^(1/2)}
Dim i As Integer
Dim DownsidePerf As Double
For i = 1 To MonthRet.Count
    
Next i

End Function

I know it's not much, but I'd really appreciate any help.

Thanks!
tom

 

[AlphaFrog]

Function DownsideDev(MonthRet As Range, MAR As Variant) As Double
    ' Calculates Downside Deviation below a Minimum Acceptable Return.
    ' {=(SUMSQ(IF(C3:C14-G2>0,0,C3:C14-G2))/COUNT(C3:C14))^(1/2)}
    Dim cell As Range
    Dim DownsidePerf As Double
    
    For Each cell In MonthRet
        If cell.Value < MAR Then DownsidePerf = DownsidePerf + (cell.Value - MAR) ^ 2
    Next cell
    DownsideDev = (DownsidePerf / Application.Count(MonthRet)) ^ 0.5
    
End Function

 

[ttbuson]

Thanks! This works great.

 

[Simulationconsultant]

Try the VBA and the spreadsheet at this link: Downside Deviation in VBA.

It gives the same results as the matrix formula in the original post