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]

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.

Okay, I'm reviewing the earlier statement now, which may answer my questions.

 

[awoohaw]

in this statement:

"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."​

I know DD is is shorthand for drawdown, but is that also idential to the values in column D?
I sort understand your three different comparison ranges, but I don't understand where the C3-Max(C$2:C3) is compared to anything.
Are wanting to compare "C3 - MAX(C$2:C3)" to the three ranges? So you will see only minimum in one cell of each of those 3 ranges?

 

[mayanksaini11]

in this statement:

"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."​

I know DD is is shorthand for drawdown, but is that also idential to the values in column D?
I sort understand your three different comparison ranges, but I don't understand where the C3-Max(C$2:C3) is compared to anything.
Are wanting to compare "C3 - MAX(C$2:C3)" to the three ranges? So you will see only minimum in one cell of each of those 3 ranges?

Column C is the cumulative value of the Portfolio. Column C and Column D are identical in the sense that based on the values of column C, the DD values are calculated in column D. If the current Portfolio value is at a peak compared to previous portfolio values there is no DD. On the other hand, if the current Portfolio value is lower than the previous portfolio's peak value then the DD figure will come from the formula =C5 - MAX(C$2:C5).
The purpose is to determine the Max DD point in each DD phase. I suppose it can be derived from column D easier than from column C.

 

[awoohaw]

again, still gibberish to me. I need to think mathematically and not invest-like. I need to know why/how values in Column D are calculated based upon what is in C.
You use "Current Portfolio Value".. is that the sum of $C$2:C"whatever day is on that line"? You also use cumulative value for C.. so that is in conflict with the last sentence since if I'm summing I am adding cumulative values over and over.

So, Current Portfolio Value which I'm thinking for EVERYDAY is column C. is at a peak?.... Peak in what range... the 3 phases you mentioned... so you want the maximum of those three sections, is that right? But how does the first formula with only the first 2 days fit into the picture? Or do you have 4 phases?

In the earlier example you used C3-Max(C2:C3) now you are using C5-Max(C2:C5)... is this always from C2 or will it be the first "C" in each phase?

I'm sure I have other questions but those are a start.

 

[awoohaw]

What happens between D13:D17??

 

[awoohaw]

the C3-Max($C$2:C3) doesn't match your column D.

mr excel questions 29.xlsm
	A	B	C	D	E	F	G
1	Trade Date	Day	Portfolio Value	DrawDown	Max DrawDown		=C3-Max($C2:C3) copy down
2	2023-03-15	Wednesday	16,848	-
3	2023-03-16	Thursday	2,075	-14,773			-14,773
4	2023-03-20	Monday	-369	-17,217	Minimum		-2,444
5	2023-03-21	Tuesday	1,313	-15,535			0
6	2023-03-23	Thursday	5,900	-10,948			0
7	2023-03-24	Friday	8,431	-8,417			0
8	2023-03-27	Monday	4,929	-11,919			-3,502
9	2023-03-28	Tuesday	1,008	-15,840			-3,921
10	2023-03-29	Wednesday	6,295	-10,553			0
11	2023-03-31	Friday	3,821	-13,027			-2,474
12	2023-04-03	Monday	18,594	-			0
13	2023-04-05	Wednesday	20,683	-			0
14	2023-04-06	Thursday	28,711	-			0
15	2023-04-11	Tuesday	33,247	-			0
16	2023-04-12	Wednesday	38,362	-			0
17	2023-04-18	Tuesday	36,708	-1,653			-1,654
18	2023-04-19	Wednesday	35,694	-2,667	Minimum		-1,014
19	2023-04-20	Thursday	43,699	-			0
20	2023-04-24	Monday	38,567	-5,132	Minimum		-5,132
21	2023-04-25	Tuesday	40,795	-2,904			0
22	2023-04-26	Wednesday	43,977	-			0
Mayanksaini11

Cell Formulas
Range	Formula
G3:G22	G3	=C3-MAX($C2:C3)

 

[awoohaw]

the C3-Max($C$2:C3) doesn't match your column D.

mr excel questions 29.xlsm
	A	B	C	D	E	F	G
1	Trade Date	Day	Portfolio Value	DrawDown	Max DrawDown		=C3-Max($C2:C3) copy down
2	2023-03-15	Wednesday	16,848	-
3	2023-03-16	Thursday	2,075	-14,773			-14,773
4	2023-03-20	Monday	-369	-17,217	Minimum		-2,444
5	2023-03-21	Tuesday	1,313	-15,535			0
6	2023-03-23	Thursday	5,900	-10,948			0
7	2023-03-24	Friday	8,431	-8,417			0
8	2023-03-27	Monday	4,929	-11,919			-3,502
9	2023-03-28	Tuesday	1,008	-15,840			-3,921
10	2023-03-29	Wednesday	6,295	-10,553			0
11	2023-03-31	Friday	3,821	-13,027			-2,474
12	2023-04-03	Monday	18,594	-			0
13	2023-04-05	Wednesday	20,683	-			0
14	2023-04-06	Thursday	28,711	-			0
15	2023-04-11	Tuesday	33,247	-			0
16	2023-04-12	Wednesday	38,362	-			0
17	2023-04-18	Tuesday	36,708	-1,653			-1,654
18	2023-04-19	Wednesday	35,694	-2,667	Minimum		-1,014
19	2023-04-20	Thursday	43,699	-			0
20	2023-04-24	Monday	38,567	-5,132	Minimum		-5,132
21	2023-04-25	Tuesday	40,795	-2,904			0
22	2023-04-26	Wednesday	43,977	-			0
Mayanksaini11

Cell Formulas
Range	Formula
G3:G22	G3	=C3-MAX($C2:C3)

I found my error here. So, I have data in column D that matches your column D. Now I just need a better understanding of how you want to populate column E with the word Minimum. YOu have row gaps in your assessment of column D.

 

[AhoyNC]

@awooha - I think what the OP is wanting is if you look at cell D2 and D11 they are 0 (or "-"). He is looking for the min of the array from D2 to D11, which is D4.
Then the next array between zeros is D16 to D19 and they want the min in this range and the next range for the min would be D19 to D22.

 

[awoohaw]

Okay, I have it 50% match. You need to tell me what is wrong.

mr excel questions 29.xlsm
	A	B	C	D	E	F
1	Trade Date	Day	Portfolio Value	DrawDown	Max DrawDown	Your Expectation
2	2023-03-15	Wednesday	16,848		Minimum
3	2023-03-16	Thursday	2,075	-14,773
4	2023-03-20	Monday	-369	-17,217	Minimum	Minimum
5	2023-03-21	Tuesday	1,313	-15,535
6	2023-03-23	Thursday	5,900	-10,948
7	2023-03-24	Friday	8,431	-8,417
8	2023-03-27	Monday	4,929	-11,919
9	2023-03-28	Tuesday	1,008	-15,840
10	2023-03-29	Wednesday	6,295	-10,553
11	2023-03-31	Friday	3,821	-13,027
12	2023-04-03	Monday	18,594	0
13	2023-04-05	Wednesday	20,683	0
14	2023-04-06	Thursday	28,711	0
15	2023-04-11	Tuesday	33,247	0
16	2023-04-12	Wednesday	38,362	0
17	2023-04-18	Tuesday	36,708	-1,654
18	2023-04-19	Wednesday	35,694	-2,668	Minimum	Minimum
19	2023-04-20	Thursday	43,699	0
20	2023-04-24	Monday	38,567	-5,132		Minimum
21	2023-04-25	Tuesday	40,795	-2,904	Minimum
22	2023-04-26	Wednesday	43,977	0
Mayanksaini11

Cell Formulas
Range	Formula
E2:E22	E2	=IF(CHOOSE(IFS(ROW(E2)<=3,4,ROW(E2)<=12,1,ROW(E2)<=17,4,ROW(E2)<=19,2,ROW(E2)=20,4,ROW(E2)<=22,3,ROW(E2)>22,4),MIN($D$4:$D$12),MIN($D$18:$D$19),MIN($D$21:$D$22),"")=D2,"Minimum","")
D3:D22	D3	=C3-MAX($C$2:C3)

 

[awoohaw]

@awooha - I think what the OP is wanting is if you look at cell D2 and D11 they are 0 (or "-"). He is looking for the min of the array from D2 to D11, which is D4.
Then the next array between zeros is D16 to D19 and they want the min in this range and the next range for the min would be D19 to D22.

I sort of figured that out. But if you see my last post, there is still some disconnect.
If you can figure it out... before a reply from OP, then that would be great.