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

 

[Peter_SSs]

@mailsmithah
Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules
Your own thread with a link back to this one was the best approach so please stick to that.