Peaks and Troughs

Status
Not open for further replies.

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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!

Annotation 2020-06-26 064953.png


Cell Formulas
RangeFormula
S2:S92S2=IF(AND((R2>R1),(R2>R3)),"peak","")
R2R2=X1+N2
R3:R93R3=R2+N3
T2:T93T2=IF(AND((R2<R1),(R2<R3)),"trough","")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A94:T200,O2:S92,A93:S93Cellcontains a blank value textNO
A94:T200,O2:S92,A93:S93Cellcontains a blank value textNO
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,125
Office Version
  1. 365
Platform
  1. Windows
@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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Status
Not open for further replies.

Forum statistics

Threads
1,144,164
Messages
5,722,855
Members
422,461
Latest member
kelleys315

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top