Dear Sir,
I'm very curious about using output from the TRANSPOSE() function as an input to the UDF function. For the first UDF below, it will work if you specify it as: =MaxDrawDown(TRANSPOSE(A11:G11)) , confirmed Ctrl+Shift+Enter. But for the second UDF, it will be an #VALUE! error if specified as =DivGrowth(TRANSPOSE(A1:A11)), confirmed with Ctrl+Shift+Enter.
Can I ask why the TRANSPOSE() worked for the first UDF, but not the second?
Function MaxDrawDown(returns As Variant) As Variant
Dim TS As Variant
Dim n As Integer
Dim Min As Double
TS = returns
n = UBound(TS)
Min = 0
For i = 1 To n
For j = i To n
temp = TS(j, 1) / TS(i, 1) - 1
On Error Resume Next
If temp < Min Then
Min = temp
End If
Next
Next
MaxDrawDown = Min
End Function
Function DivGrowth(Rng As Range)
Dim i, start, pos As Integer
For i = 1 To Rng.Cells.Count
If IsNumeric(Rng(i).Value) And Rng(i).Value <> "" Then
start = i
Exit For
End If
Next i
For i = start To Rng.Cells.Count
If Val(Rng(i)) > Val(Rng(i - 1)) Then pos = pos + 1
Next i
DivGrowth = pos
End Function
Bookmarks