Calculating various drawdown metrics

[Median]

Hi everyone,

I'm looking to calculate the following figures from my dataset (included below, Col B):

1. "Max drawdown (sum)"
2. "Max drawdown (length)"
3. "Avg. drawdown (sum)"
4. "Avg. drawdown (length)"

A "drawdown" is anytime the graph is trending downwards (i.e. negative gradient)

Couple of screenies

Max drawdown, visually:

MDGASDHNQSmibpW7NMQY4Q.png

Drawdown sum (used to calc the avg)

bEEPBpdLR_qBJPa7xxfvJQ.png

Drawdown length (used to cal avg)

zkJpgeUfRtuQn8OEnR_Wnw.png

Hope that wasn't to rambly

Any questions/clarifications do leave em below

Ty

Mdn

[Bo_Ry]

Please try

1. "Max drawdown (sum)"
=MIN(INDEX(SUBTOTAL(9,OFFSET(B1,AGGREGATE(15,6,ROW(B1:B999)/(D1:D999<>D2:D1000),ROW(INDIRECT("1:"&SUMPRODUCT(--(D1:D999>D2:D1000))))*2-1),,MMULT(AGGREGATE(15,6,ROW(B1:B999)/(D1:D999<>D2:D1000),ROW(INDIRECT("1:"&SUMPRODUCT(--(D1:D999>D2:D1000))))*2-{1,0}),{-1;1}))),))


2. "Max drawdown (length)"
=MAX(MMULT(AGGREGATE(15,6,ROW(B1:B999)/(D1:D999<>D2:D1000),ROW(INDIRECT("1:"&SUMPRODUCT(--(D1:D999>D2:D1000))))*2-{1,0}),{-1;1}))

3. "Avg. drawdown (sum)"
=AVERAGE(INDEX(SUBTOTAL(9,OFFSET(B1,AGGREGATE(15,6,ROW(B1:B999)/(D1:D999<>D2:D1000),ROW(INDIRECT("1:"&SUMPRODUCT(--(D1:D999>D2:D1000))))*2-1),,MMULT(AGGREGATE(15,6,ROW(B1:B999)/(D1:D999<>D2:D1000),ROW(INDIRECT("1:"&SUMPRODUCT(--(D1:D999>D2:D1000))))*2-{1,0}),{-1;1}))),))


4. "Avg. drawdown (length)"
=AVERAGE(MMULT(AGGREGATE(15,6,ROW(B1:B999)/(D1:D999<>D2:D1000),ROW(INDIRECT("1:"&SUMPRODUCT(--(D1:D999>D2:D1000))))*2-{1,0}),{-1;1}))