High/Low Balance

[Steve Voltage]

Anyone know of a way to get the highest and lowest balance using excel ? A condition would be that your exposure = 0. The reason for this would be that i could set my stake to change when my balance reached a new high or low.


I thought i had cracked it with the following code but all this does is record the new high/low balance in column Z even if the exposure does not = 0 for some reason.

Private Sub Worksheet_Calculate()
Dim LR As Long
LR = Me.Range("Z" & Rows.Count).End(xlUp).Row
If Range("AL2").Value = 0 Then

If Range("AI2").Value > Range("Z" & LR).Value Then Range("Z" & LR + 1).Value = Range("AI2").Value
End If
If Range("AI2").Value < Range("Z" & LR).Value Then Range("Z" & LR + 1).Value = Range("AI2").Value
End Sub

Any help please.

[osknows]

You need to move the End if statement down a few lines to ensure it doesn't fire exposure = 0

Code: Select all

Private Sub Worksheet_Calculate()
Dim LR As Long
LR = Me.Range("a" & Rows.Count).End(xlUp).Row
If Range("e1").Value = 0 Then

If Range("d1").Value > Range("a" & LR).Value Then Range("a" & LR + 1).Value = Range("d1").Value

If Range("d1").Value < Range("a" & LR).Value Then Range("a" & LR + 1).Value = Range("d1").Value

End If
End Sub


An easier alternative you may want to consider:
Set J2 trigger to U to update the balance and exposure in cells I2 & L2 after each refresh then using normal excel formula

Starting bank = A fixed amount you must update when you start BA (usually equal to your current balance)
Current bank = Current balance - Current exposure (CE is negative)