Thread: High Water Mark(Draw down Calculation)

It's not clear to me if you were looking to compress all of your calcs into one calculation... that was my take...

If we use the data in A & B in your sample file then set:

Code:

C3: 1

C4:
=IF($B4>IF(COUNT($C$3:$C3)=1,$B3,LOOKUP(9.99999999999999E+307,$C$3:$C3,$B$3:$B3)),IF(COUNT($C$3:$C3)=1,$B4,LOOKUP(9.99999999999999E+307,$C$3:$C3,$B$3:$B3))-MIN(INDEX($B$3:$B3,MATCH(9.99999999999999E+307,$C$3:$C3)):$B3),"")
copied down

Based on my analysis C will replicate your results in N without need for any data other than that stored in B.

Was the above approach your intention ?

In hindsight this is a little simpler than most Local Min / Max analysis given the new Max is not determined by a threshold from the last Local Min rather it is simply when the value exceeds last Max, meaning it must always be the biggest number thus far... we can therefore simplify somewhat to:

Code:

C4
=IF(AND($A4>1,$B4>MAX($B$3:$B3)),MAX($B$3:$B3)-MIN(INDEX($B$3:$B3,MATCH(9.99999999999999E+307,$C$3:$C3)):$B3),"") 
copied down