Loss Deviation

[Jason]

I don't have that much VBA experience so have a simple
question. I am trying to set up a user-defined function
to simplify a two-part calculation to calculate "loss
deviation", which is similar to standard deviation (of a
set of investment returns) except that it throws out all
positive values (and therefore calculates standard
deviation of all the losses only).

So the first part of my function needs to look at all of
the values in a range and then isolate the losses to
create a new range (this is what I can't figure out how
to do). Then, the second part of my new function can
calculate a standard deviation based on that new range.

Mathematically:

Where N = Number of Periods

Where Ri = Return for period I

Where Ml = Loss Mean

Where Li = Ri ( IF Ri < 0 )or 0 ( IF Ri >= 0 )

Where LLi = Ri - Ml ( IF Ri < 0 ) or 0 ( IF Ri >=
0 )

Nl = Number of periods that Ri < 0

Ml = (Sum for 1...I of Li) / Nl

Loss Deviation = sqrt( Sum for 1...I ( LLi)^2 / (Nl -
1))

I can do this in excel by laying out a second column
which refers to each cell in the first column and makes
all the positive values 0 using the Min function. But I
don't know how to automate this using VBA. Many thanks
for the help with this.

 

[merjet]

Function LossDeviation(Target As Range) As Double
Dim dNum As Double
Dim iDen As Integer
Dim c As Range

For Each c In Target
If c.Value < 0 Then
dNum = dNum + c.Value ^ 2
iDen = iDen + 1
End If
Next c
If iDen > 1 Then LossDeviation = (dNum / (iDen - 1)) ^ 0.5
End Function

HTH,
Merjet