Identifying PEAKS and TROUGHS in a data series

[eggsell]

Hi
Using: Excel XP, Windows XP

Is there a way to identify peaks and troughs in a data series? Say, I have these values in a column: 8, 7, 6, 7, 8, 7, 6, 5, 4, 3, 4, 5, 6, 7, 8, 9, and 8
The objective is to identify 6 and 3 as troughs and 8 and 9 as peaks. I have been unable to solve this problem using SMALL/LARGE, as they only identify the kth smallest/largest numbers and not the troughs/peaks.

In a nutshell, I would like to devise a formula that would allow me to identify (x) number of lowest troughs and (x) number of highest peaks in a dynamic range. Please note that numbers that are lower than the second trough or higher than second peak but themselves are not troughs or peaks (i.e. numbers in the immediate vicinity of the lowest trough or highest peak) need to be ignored. In other words, a change of direction is required in order for a number to be considered as a peak or trough.

So far it seems that this is beyond my abilities; while I still work on it, I would be grateful if someone could point me in the right direction.

 

[summerguy]

Hi Paddy,

Yes - peaks and troughs are as usual and demonstrated in the spreadsheet.

Recovery is the first instance where all previous losses are made up i.e. draw down = exactly 0 after a trough.

Thanks

 

[summerguy]

After much trial and error I find the formula to identify peaks, troughs and recovery. I have one issue in Cell D12 where the formula does not identify the trough. I have spent quite a bit of time staring at this and cant see where the formula is wrong. I appreciate any help on this.

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:49px;" /><col style="width:56px;" /><col style="width:75px;" /><col style="width:134px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-size:10pt; text-align:center; ">Date</td><td style="font-weight:bold; font-size:10pt; text-align:center; ">Value</td><td style="font-weight:bold; font-size:10pt; text-align:center; ">DrawDown</td><td style="font-weight:bold; font-size:10pt; text-align:center; ">Peak/Trough/Recovery</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">Jan-08</td><td style="font-size:10pt; text-align:right; ">100.00</td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">Feb-08</td><td style="font-size:10pt; text-align:right; ">102.00</td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; ">Peak</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">Mar-08</td><td style="font-size:10pt; text-align:right; ">95.00</td><td style="color:#800080; font-size:10pt; text-align:right; ">-6.86%</td><td style="font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">Apr-08</td><td style="font-size:10pt; text-align:right; ">90.00</td><td style="color:#800080; font-size:10pt; text-align:right; ">-11.76%</td><td style="font-size:10pt; ">Trough</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">May-08</td><td style="font-size:10pt; text-align:right; ">98.00</td><td style="color:#800080; font-size:10pt; text-align:right; ">-3.92%</td><td style="font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">Jun-08</td><td style="font-size:10pt; text-align:right; ">99.00</td><td style="color:#800080; font-size:10pt; text-align:right; ">-2.94%</td><td style="font-size:10pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">Jul-08</td><td style="font-size:10pt; text-align:right; ">105.00</td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; ">Recovery</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">Aug-08</td><td style="font-size:10pt; text-align:right; ">107.00</td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; ">Peak</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">Sep-08</td><td style="font-size:10pt; text-align:right; ">103.00</td><td style="color:#800080; font-size:10pt; text-align:right; ">-3.74%</td><td style="font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">Oct-08</td><td style="font-size:10pt; text-align:right; ">102.00</td><td style="color:#800080; font-size:10pt; text-align:right; ">-4.67%</td><td style="font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">Nov-08</td><td style="font-size:10pt; text-align:right; ">99.00</td><td style="color:#800080; font-size:10pt; text-align:right; ">-7.48%</td><td style="background-color:#ffff00; font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">Dec-08</td><td style="font-size:10pt; text-align:right; ">101.00</td><td style="color:#800080; font-size:10pt; text-align:right; ">-5.61%</td><td style="font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:right; ">Jan-09</td><td style="font-size:10pt; text-align:right; ">107.00</td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; ">Recovery</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">Feb-09</td><td style="font-size:10pt; text-align:right; ">   110.00 </td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; ">Peak</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">Mar-09</td><td style="font-size:10pt; text-align:right; ">   106.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-3.64%</td><td style="font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">Apr-09</td><td style="font-size:10pt; text-align:right; ">   100.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-9.09%</td><td style="font-size:10pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:right; ">May-09</td><td style="font-size:10pt; text-align:right; ">     90.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-18.18%</td><td style="font-size:10pt; ">Trough</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:right; ">Jun-09</td><td style="font-size:10pt; text-align:right; ">   110.00 </td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; ">Recovery</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; text-align:right; ">Jul-09</td><td style="font-size:10pt; text-align:right; ">   115.00 </td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; ">Peak</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; text-align:right; ">Aug-09</td><td style="font-size:10pt; text-align:right; ">   112.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-2.61%</td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >

<b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=(B2-MAX<span style=' color:008000; '>(B2:$B$2)</span>)/MAX(B2:$B$2)</td></tr><tr><td >D2</td><td >=IF(AND<span style=' color:008000; '>(C2>=MAX<span style=' color:#0000ff; '>(C$2:C3)</span>,C2>C3)</span>,"Peak",IF<span style=' color:008000; '>(AND<span style=' color:#0000ff; '>(C2<=MIN<span style=' color:#ff0000; '>(C$2:C3)</span>,C2<C3)</span>,"Trough",IF<span style=' color:#0000ff; '>(AND<span style=' color:#ff0000; '>(C1<0,C2>=0)</span>,"Recovery","")</span>)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000;

 

[summerguy]

I also notice that my formula gives me two troughs in cases such as the below. I only want to get the actual trough though.

><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:49px;" /><col style="width:56px;" /><col style="width:75px;" /><col style="width:134px;" /><col style="width:52px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">Mar-09</td><td style="font-size:10pt; text-align:right; "> * 106.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-3.64%</td><td style="font-size:10pt; ">*</td><td style="font-size:10pt; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">Apr-09</td><td style="font-size:10pt; text-align:right; "> * 100.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-9.09%</td><td style="font-size:10pt; ">*</td><td style="font-size:10pt; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:right; ">May-09</td><td style="font-size:10pt; text-align:right; "> * * 90.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-18.18%</td><td style="background-color:#ffff00; font-size:10pt; ">Trough</td><td style="font-size:10pt; text-align:right; ">May-09</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:right; ">Jun-09</td><td style="font-size:10pt; text-align:right; "> * * 92.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-16.36%</td><td style="font-size:10pt; ">*</td><td style="font-size:10pt; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; text-align:right; ">Jul-09</td><td style="font-size:10pt; text-align:right; "> * * 89.00 </td><td style="color:#800080; font-size:10pt; text-align:right; ">-19.09%</td><td style="background-color:#ffff00; font-size:10pt; ">Trough</td><td style="font-size:10pt; text-align:right; ">Jul-09</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; text-align:right; ">Aug-09</td><td style="font-size:10pt; text-align:right; "> * 112.00 </td><td style="font-size:10pt; text-align:right; ">0.00%</td><td style="font-size:10pt; ">Recovery</td><td style="font-size:10pt; ">*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr>

 

[PaddyD]

This dropped off my radar - do you still need a solution?

 

[Platinum1]

This dropped off my radar - do you still need a solution?

Hello,

I found this thread by chance and am looking for a similar soultion as posted above. I have used the formula mentioned but I still miss the odd peak and trough and occassionaly find two in a row.

Any help on the last question would be appreciated.

Thanks