I'm attempting a function that has a loop within a loop, to iterate an
optimal value. I'm not seeing why it's not calculating. It returns
#value!
On spreadsheet, the following cashflow
100
-500
1500
-600
Option Explicit
Option Base 1
Public Function OptimalF(ByVal MyCells As Double) As Double
'/Iterates thru possible F values, bound .01 to .99 finding the
highest TWR
Dim FVariable As Double
Dim LargestLoss As Long
Dim TWRs(99) As Double
Dim nRow As Integer
Dim TradeResult As Double
Dim Count As Integer
Dim RowCount As Integer
Dim TWR() As Double
Dim MyCells() As Long
LargestLoss = Application.WorksheetFunction.Min(MyCells)
RowCount = MyCells.Count
With MyCells
ReDim MyCells(RowCount) As Single
ReDim TWR(RowCount) As Double
Count = 1
For FVariable = 0.01 To 0.99 Step 0.01
For nRow = 1 To RowCount Step 1
.Cells(nRow, 1).Value = TradeResult
TWR(nRow) = 1 + (FVariable * (-TradeResult / LargestLoss))
Next nRow
TWRs(Count) = TWR(RowCount)
If TWRs(Count) < TWRs(Count - 1) Then
OptimalF = FVariable
Exit Function
End If
Count = Count + 1
Next FVariable
End With
OptimalF = FVariable
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
