I have the following code that checks four different conditions, and gives me the cumulative value for the cells that meet each condition. I would like to add a counter that only counts the cells that meet the specific condition. Below, I have market that counter with k. For example, if UpDown=1, I want the counter to count only the number of cells that meet that condition. Currently, my code does not work that way. Any help would be appreciated!
Public Function Test(UpDown As Double, rng_port As Range, rng_bench As Range) As Double
Dim l As Long, temp As Double, k as integer
k=0
If rng_port.Count <> rng_bench.Count Then upcaptureratio = xlErrValue
cumret_port = 1
For l = 1 To rng_bench.Count
If UpDown = 1 Then
temp = 1 + IIf(rng_bench.Cells(l) >= 0.0025, rng_port.Cells(l), 0)
k=k+1
ElseIf UpDown = 2 Then
temp = 1 + IIf(rng_bench.Cells(l) < 0.0025 And rng_bench.Cells(l) >= 0, rng_port.Cells(l), 0)
k=k+1
ElseIf UpDown = 3 Then
temp = 1 + IIf(rng_bench.Cells(l) < 0 And rng_bench.Cells(l) >= -0.0025, rng_port.Cells(l), 0)
k=k+1
ElseIf UpDown = 4 Then
temp = 1 + IIf(rng_bench.Cells(l) < -0.0025, rng_port.Cells(l), 0)
k=k+1
End If
cumret_port = cumret_port * temp
Next
Test = cumret_port^(1/k) - 1
Aucun commentaire:
Enregistrer un commentaire