Try this one:

=MAX(IF($A$2:$A$7<OFFSET($A$2:$A$7,-1,0),IFERROR(1-$A$2:$A$7/OFFSET($A$2:$A$7,-1,0),0),0))

Array formula, so use Ctrl+Shift+Enter, not just enter.

Thank you very much for your answer

your formula is very good to calculate percentage "25%"

but it is half of my question , how can I get to formula for show "500$"

the maximum drop is 25% and that value is 500$

Thank you for your kindness

The question and topic title clearly says:

I need formula to calculate the maximum percentage of drop

Looks like the question is in half, not the response 🙂

Use this formula in F1:

=MIN(IF(IFERROR(1-$A$2:$A$7/OFFSET($A$2:$A$7,-1,0),0)=MAX(IF($A$2:$A$7<OFFSET($A$2:$A$7,-1,0),IFERROR(1-$A$2:$A$7/OFFSET($A$2:$A$7,-1,0),0),0)),ROW($A$2:$A$7),""))

in G2:

=1-INDEX($A:$A,F1)/INDEX($A:$A,F1-1)

in G3:

=INDEX($A:$A,F1-1)-INDEX($A:$A,F1)

Sorry for my little explain
and thank you very very very much
God bless to your life

Hello,

I have another problem and I updated the file 

I have more variables to calculate also include empty cells but dot not consider empty cells or that cells has no variables

I apologize for the complexity of the question

Thank you in advance for your reply

In your example, 500 and 25% is based on range A3:A6, considering cells A3=2000 and A6=1500.

Ca you explain the logic that isolates these cells as the correct result? Makes no sense to me as they are not consecutive, and A5 is greater than A4

Thank you for your time

Qoutation : "In your example, 500 and 25% is based on range A3:A6, considering cells A3=2000 and A6=1500." Its TRUE

Explain for LOGIC : in the stock market we have 2000$ , in the 1st trade we loss 300$=1700$ and in the 2nd trade we earn 100$=1800$, and in the 3rd trade we loss again 300$=1500$ "how much is the maximum loss of main capital : it is 500$ = 2000-1500=500"

your right, the consicutive is not correct word in this situation but it is common in the stock market

keep in mind the help file that I uploaded in this post is just a hint and it is vice versa of the my question

my question is maximum % retrace and the help file is maximum $ retrace "vice versa"

Thank you for your kindness

You already have a solution in the file you uploaded, an additional column as you have used is not a bad thing.

Seems you have the results you wanted in the G2-G3 cells, what's wrong with those formulas, why don't you use those?

Dear Sir, 

In the last file that I uploaded the formula is true but it is for maximum drawdown "900$ - 30%" I use it

but I need another formula to calculate different result which mean is "relative drawdown"

in the last file :

maximum drawdown is : 900$ and 30% "percentage of maximum value"

relative drawdown is : 40% and 800$ "I need formula to calculate this one" "value of maximum percent"

I uploaded agin with a little changes

Please look this file and how can I get to 40%

I'm so sorry for the complexity

Thank you very much

You can use a simple formula in B2, copied down:

=IF(C2<0,1-(A2+C2)/A2,0)