High Water Mark(Draw down Calculation)

sajeel · #1 07-02-2009, 02:34 PM

Trying to use the High water Mark method in order to establish a draw down, from new high to low.In the sheet attached looking at "Simple Example" Column V gives the Time series. So the highs occur at 36,48,56,61,70 and 75.What I am trying to calculate is the lowest values between 2 highs. For example between 1st High(36) and 2nd High(48) the minimum value was 12.Between the 2nd High (48) and 3rd High(56) the minimum value was 20 and so on. Thus the draw down between 1st and 2nd High was 1st High-Min value(1st high-2ndhigh)=36-12=24.

Thus in a whole time series I need to produce an automated calculation rather than manually looking at the values and by hand selecting the ranges.

I have made an attempt with the complex example side.

DonkeyOte · #2 07-02-2009, 03:00 PM

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

DonkeyOte · #3 07-02-2009, 03:12 PM

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

sajeel · #4 07-04-2009, 03:11 PM

Thanks a lot works perfectly!

Welcome to the Excel Forum

Forum Statistics