Find the Maximum number in a series of Data

[mayanksaini11]

I have an Excel file in which I record my equity Portfolio value on a daily basis at the end of every day. I have a column named 'Drawdown' where I calculate the portfolio value from the peak portfolio value. When the value is below zero that means my portfolio is running below the peak. On the contrary, where the Drawdown value is zero, it means that the portfolio value is at its peak.
Now with this, I have a series of values in the Drawdown column. What I am trying to do is to find a minimum value in a series of values whenever I am in the drawdown phase. The output that I am trying to display in column E is mentioned below: Please suggest to me the formula to display the below output in column E in excel 2010.

A1	B1	C1	D1	E1
Trade Date	Day	Portfolio Value	DrawDown	Max DrawDown
15/03/2023	Wednesday	16,848	-
16/03/2023	Thursday	2,075	-14,773
20/03/2023	Monday	-369	-17,217	Minimum
21/03/2023	Tuesday	1,313	-15,535
23/03/2023	Thursday	5,900	-10,948
24/03/2023	Friday	8,431	-8,417
27/03/2023	Monday	4,929	-11,919
28/03/2023	Tuesday	1,008	-15,840
29/03/2023	Wednesday	6,295	-10,553
31/03/2023	Friday	3,821	-13,027
03/04/2023	Monday	18,594	-
05/04/2023	Wednesday	20,683	-
06/04/2023	Thursday	28,711	-
11/04/2023	Tuesday	33,247	-
12/04/2023	Wednesday	38,362	-
18/04/2023	Tuesday	36,708	-1,653
19/04/2023	Wednesday	35,694	-2,667	Minimum
20/04/2023	Thursday	43,699	-
24/04/2023	Monday	38,567	-5,132	Minimum
25/04/2023	Tuesday	40,795	-2,904
26/04/2023	Wednesday	43,977	-

 

[awoohaw]

so you are asking for the "minimum" or "MaxDrawDown" to appear in Column E?
And just compare column C to column D?

 

[awoohaw]

what determines how minimum is to appear, you only have one Value in column C that is negative, and many more than 3 in column D that are negative, but only 3 "minimum" in column E.

 

[mayanksaini11]

so you are asking for the "minimum" or "MaxDrawDown" to appear in Column E?
And just compare column C to column D?

Yes, trying to calculate the max drawdown. Each DD phase has a separate minimum value 0 max DD. Like in my data, there are 3 DD phases that have 3 different max DD values.

 

[awoohaw]

Yes, trying to calculate the max drawdown. Each DD phase has a separate minimum value 0 max DD. Like in my data, there are 3 DD phases that have 3 different max DD values.

Can you restate that, but using column numbers instead of DD, Value, or Phase? I just see column C and column D, and I cannot figure out what you want to have done.

 

[awoohaw]

is the Max that is being compared somewhere else? I thought that is what you wanted calculated, but I don't know how to come up with something that says if C is less than D, then Mimimum, or D is less than C then Minimum because the examples do not fit that arithmetic.

 

[awoohaw]

24/04/2023 Monday 38,567 -5,132 Minimum
25/04/2023 Tuesday 40,795 -2,904

For instance, why is 24/4 in minimum but 25/4 not in minimum? There is something missing in how you determinie the minimum that you have not explained very clearly.

 

[mayanksaini11]

ach DD phase has a separate min

what determines how minimum is to appear, you only have one Value in column C that is negative, and many more than 3 in column D that are negative, but only 3 "minimum" in column E.

If you try to calculate based on Column C then in the 1st DD phase the peak was 16848 which came down to -369 (Max DD point in the 1st DD phase). Similarly, in the 2nd DD phase, the peak was 38362 which came down to 35694 (Max DD point in the 2nd DD phase) before it made another peak. Likewise in the 3rd DD phase, the peak was 43699 which came down to 38567 (Max DD point in the 3rd DD phase)

 

[mayanksaini11]

is the Max that is being compared somewhere else? I thought that is what you wanted calculated, but I don't know how to come up with something that says if C is less than D, then Mimimum, or D is less than C then Minimum because the examples do not fit that arithmetic.

In the Excel file column D is derived from formulas =C3 - MAX(C$2:C3). The comparison is to happen within the series of DD, 1st phase of DD is within Cells D4 to D12, 2nd phase of DD is within Cells D18 to D19 and then 3rd phase of DD is within Cells D21 to D22.

 

[mayanksaini11]

24/04/2023 Monday 38,567 -5,132 Minimum
25/04/2023 Tuesday 40,795 -2,904

For instance, why is 24/4 in minimum but 25/4 not in minimum? There is something missing in how you determinie the minimum that you have not explained very clearly.

on 24/4 The portfolio value made a Max DD point or Lowest Point or Minimum point i.e. 38567 of Portfolio value in the 3rd DD phase. on 25/04 the Portfolio value recovered to 40795 which is up from the lowest point. Therefore 24/04 is the Max DD point and not 25/04. I hope I could make my point clear.