Drawdown Nightmare

[oskarrakso]

Before I start - sorry for my mistakes in English.

In C1 I would like count drawdown from fixed range (offset from A10), I would like to copy that formula to C2 etc.

If A10 = 3 range would be B1:B4 that's easy (40-20) = 50% drawdown. What if A10 = 6? (Range would be B1:B7. Simple max or mins will not work because lowest value is older than peak.

That drives me crazy. Help please.

 

[oskarrakso]

Thanks Alex but formula doesnt work.
I mean it work on that sample data, but there are problems with new data.
Example
B1: 20
B2: 40
B3: 19
B4: 200
B5: 1
B6: 100

Correct answer is 99%,
This formula calculates 200 and 19 not 100 and 1.

I think its immposible to make it on Excel.