Peaks and Troughs

[andrewmurray86]

Hello Everyone I'm hoping you might help me with a formula to identify peaks and troughs

I've got a formula that finds that maximum drawdown from the initial balance, But I need to find the value to get the maximum drawdown overall (At this time it is the maximum from the initial balance.
And I need to find the draw down between a new high then the low before a new high.

The graph should help

This graph is generated from the data I will use to get the formula. The information will sit off the side for easy reference, I have also included the data with peak and trough labeled on it, but I'm just after the values as described above, not every peak and trough, you can see there are many but essentially just the ones labelled in the graph

Thanks so much everyone!

Copy of Andrew_Murray_Trade_Results_2020-1.xlsx
	R	S	T
1	Equity
2	3995.97
3	3991.93
4	3988.28
5	3984.64
6	3980.39
7	3976.13
8	3936.21
9	3896.3
10	3896.14
11	3895.98
12	3867.21
13	3838.39
14	3798
15	3757.61
16	3716.97
17	3676.33
18	3675.41
19	3675.09		trough
20	3681.66
21	3688.27
22	3764.01
23	3788.03
24	3812.48
25	3812.78	peak
26	3812.3		trough
27	3906.41
28	3931.76
29	3949.48
30	4010.59	peak
31	4007.91		trough
32	4008.19
33	4030.97
34	4031.25
35	4053.77	peak
36	4053.31
37	4053.07
38	4052.7
39	4052.45
40	4017.59
41	3982.72
42	3982.09		trough
43	3999.01
44	4015.93
45	4032.77
46	4049.6	peak
47	4048.68
48	4048.31
49	3948.31
50	3928.31
51	3928.14		trough
52	3950.34
53	3951.16
54	3952.04
55	3976.97	peak
56	3960.06
57	3943.26		trough
58	3966.08
59	3977.22
60	3977.82
61	4000.62
62	4024.03
63	4040.33	peak
64	4039.76		trough
65	4060.98
66	4061.35	peak
67	4059.7
68	4059.12
69	4058.87
70	4058.61
71	4016.17
72	3973.76
73	3973.47
74	3940.03
75	3906.59		trough
76	3932.69	peak
77	3932.2
78	3864.2
79	3861.67		trough
80	3887.37	peak
81	3885.99
82	3884.94
83	3883.92
84	3882.93
85	3881.94		trough
86	3882.93	peak
87	3879.87
88	3879.08
89	3878.04
90	3877.05
91	3876.22
92	3873.17
93	3873.17
Splash

Cell Formulas
Range	Formula
S2:S92	S2	=IF(AND((R2>R1),(R2>R3)),"peak","")
R2	R2	=X1+N2
R3:R93	R3	=R2+N3
T2:T93	T2	=IF(AND((R2<R1),(R2<R3)),"trough","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A94:T200,O2:S92,A93:S93	Cell	contains a blank value	text	NO
A94:T200,O2:S92,A93:S93	Cell	contains a blank value	text	NO

 

[offthelip]

try this:
=MAX(R3:R27)-MIN(R3:R27)

 

[lrobbo314]

Like this?

Book3.xlsm
	A	B	C
1	ID	Equity	PT
2		3995.97
3		3991.93
4		3988.28
5		3984.64
6		3980.39
7		3976.13
8		3936.21
9		3896.3
10		3896.14
11		3895.98
12		3867.21
13		3838.39
14		3798
15		3757.61
16		3716.97
17		3676.33
18		3675.41
19		3675.09	Trough
20		3681.66
21		3688.27
22		3764.01
23		3788.03
24		3812.48
25		3812.78	Peak
26		3812.3	Trough
27		3906.41
28		3931.76
29		3949.48
30		4010.59	Peak
31		4007.91	Trough
32		4008.19
33		4030.97
34		4031.25
35		4053.77	Peak
36		4053.31
37		4053.07
38		4052.7
39		4052.45
40		4017.59
41		3982.72
42		3982.09	Trough
43		3999.01
44		4015.93
45		4032.77
46		4049.6	Peak
47		4048.68
48		4048.31
49		3948.31
50		3928.31
51		3928.14	Trough
52		3950.34
53		3951.16
54		3952.04
55		3976.97	Peak
56		3960.06
57		3943.26	Trough
58		3966.08
59		3977.22
60		3977.82
61		4000.62
62		4024.03
63		4040.33	Peak
64		4039.76	Trough
65		4060.98
66		4061.35	Peak
67		4059.7
68		4059.12
69		4058.87
70		4058.61
71		4016.17
72		3973.76
73		3973.47
74		3940.03
75		3906.59	Trough
76		3932.69	Peak
77		3932.2
78		3864.2
79		3861.67	Trough
80		3887.37	Peak
81		3885.99
82		3884.94
83		3883.92
84		3882.93
85		3881.94	Trough
86		3882.93	Peak
87		3879.87
88		3879.08
89		3878.04
90		3877.05
91		3876.22
92		3873.17
93		3873.17
Sheet4

Cell Formulas
Range	Formula
C3:C93	C3	=IF(AND(B3<B2,B3<B4),"Trough",IF(AND(B3>B2,B3>B4),"Peak",""))

 

[andrewmurray86]

Thanks everyone that's really great. I am wondering if there is a way we can tweak the formula to only give the extremes like in the graph I posted? Or even using the data from
Irobbo314's formula

Cell Formulas
Range	Formula
C3:C93	C3	=IF(AND(B3<B2,B3<B4),"Trough",IF(AND(B3>B2,B3>B4),"Peak",""))

to give the "gap" between the lowest and highest after a peak, if we treat the start balance of 4000 as a peak, then the lowest point from there is 8.12% lower

I'm thinking if, for example" if C3= peak then find next trough and calculate the the difference, the give the max difference between the peak and trough". The idea is to eliminate the smaller peak-trough fluctuations so after the peak to trough of c3 to c19, the next one would be peak at c35 and trough at c51, then peak at c66 and trough at c92

Is such a thing possible?

 

[lrobbo314]

This seems better.

Book3.xlsm
	A	B	C	D	E
1	ID	Equity	Combo	Valleys	Peaks
2		3995.97
3		3991.93
4		3988.28
5		3984.64
6		3980.39
7		3976.13
8		3936.21
9		3896.3
10		3896.14
11		3895.98
12		3867.21
13		3838.39
14		3798
15		3757.61
16		3716.97
17		3676.33
18		3675.41
19		3675.09	Valley	Valley
20		3681.66
21		3688.27
22		3764.01
23		3788.03
24		3812.48
25		3812.78
26		3812.3	Valley	Valley
27		3906.41
28		3931.76
29		3949.48
30		4010.59	Peak		Peak
31		4007.91
32		4008.19
33		4030.97
34		4031.25
35		4053.77	Peak		Peak
36		4053.31
37		4053.07
38		4052.7
39		4052.45
40		4017.59
41		3982.72
42		3982.09
43		3999.01
44		4015.93
45		4032.77
46		4049.6	Peak		Peak
47		4048.68
48		4048.31
49		3948.31
50		3928.31
51		3928.14	Valley	Valley
52		3950.34
53		3951.16
54		3952.04
55		3976.97	Peak		Peak
56		3960.06
57		3943.26
58		3966.08
59		3977.22
60		3977.82
61		4000.62
62		4024.03
63		4040.33	Peak		Peak
64		4039.76
65		4060.98
66		4061.35	Peak		Peak
Sheet4

Cell Formulas
Range	Formula
C2:C66	C2	=IF(D2<>"",D2,IF(E2<>"",E2,""))
D3:D66	D3	=IF(AND(B2>B3,B4>B3),IF(B3<AVERAGE($B$2:$B$93),"Valley",""),"")
E3:E66	E3	=IF(AND(B2<B3,B4<B3),IF(B3>AVERAGE($B$2:$B$93),"Peak",""),"")

 

[lrobbo314]

And if that works for you then the 3 columns of formulas can be condensed to just 1 formula.

=IF(AND(B2>B3,B4>B3),IF(B3<AVERAGE($B$2:$B$93),"Valley",""),IF(AND(B2<B3,B4<B3),IF(B3>AVERAGE($B$2:$B$93),"Peak",""),""))

 

[lrobbo314]

I'm not sure how to replicate the results you said exactly, but playing with these percentages gets closer. I'm calling it quits for today, so here's another version of the formula that you can tinker with to tune it to your liking.

=IF(AND(B2>B3,B4>B3),IF(B3<MIN($B$2:$B$93)/0.93,"Valley",""),IF(AND(B2<B3,B4<B3),IF(B3>MAX($B$2:$B$93)*0.995,"Peak",""),""))

 

[andrewmurray86]

Thanks very much! I appreciate your help immensley

 

[andrewmurray86]

Hi guys, I've got it down to a nice point. I'm just wondering if there is a way to get the values highlighted?

I'm using this formula, on another sheet, =IF(Splash!S3:S252="Valley", MIN(Splash!R3:R252),"")

But I am getting a blank cell

 

[mailsmithah]

And if that works for you then the 3 columns of formulas can be condensed to just 1 formula.

=IF(AND(B2>B3,B4>B3),IF(B3<AVERAGE($B$2:$B$93),"Valley",""),IF(AND(B2<B3,B4<B3),IF(B3>AVERAGE($B$2:$B$93),"Peak",""),""))

Hi i am facing similar issue of identifying peaks and troughs, but if there are continous peak or trough values then the
Formula is not identifying, any pointers on
This can be helpful.