Finding highest drop in value

[capex]

I need some help to automate process of finding largest drop percentages in a series based on following criteria:

1. These two columns represent S&P 500 value for last few months.
2. I am trying to find out highest drop in the series.
3. Drop in value starts if value is lower than previous day.
4. Drop in value stops as value goes up from previous day.
5. Example provided shows value drop in B3 from previous day B2, that event started the series and ended on B6 where value increased from previous day B5.
6. Highlighted cells is where drop started and ended with next day going up higher than previous day.
7. I am trying to find a formula which would find out
A. Each drop
B. Present only highest drop based on above mention conditions which is -11.18% from 8/3/2011 to 8/8/2011. Only interested in highest drop.
8. This series will continue daily as it unfolds, there could be many small drops along the way, want to see only highest one till another one goes higher than current one.

Thank you in advance for helping me out.

AD


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">Adj Close</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">8/3/2011</td><td style="text-align: right;;">1260.34</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">8/4/2011</td><td style="text-align: right;background-color: #FFFF00;;">1200.07</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">8/5/2011</td><td style="text-align: right;background-color: #FFFF00;;">1199.38</td><td style="text-align: right;;">-11.18%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">8/8/2011</td><td style="text-align: right;background-color: #FFFF00;;">1119.46</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">8/9/2011</td><td style="text-align: right;;">1172.53</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">8/10/2011</td><td style="text-align: right;background-color: #FFFF00;;">1120.76</td><td style="text-align: right;;">-4.62%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">8/11/2011</td><td style="text-align: right;;">1172.64</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8/12/2011</td><td style="text-align: right;;">1178.81</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">8/15/2011</td><td style="text-align: right;;">1204.49</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8/16/2011</td><td style="text-align: right;background-color: #FFFF00;;">1192.76</td><td style="text-align: right;;">-0.98%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">8/17/2011</td><td style="text-align: right;;">1193.89</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">8/18/2011</td><td style="text-align: right;background-color: #FFFF00;;">1140.65</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">8/19/2011</td><td style="text-align: right;background-color: #FFFF00;;">1123.53</td><td style="text-align: right;;">-6.26%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">8/22/2011</td><td style="text-align: right;;">1123.82</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">8/23/2011</td><td style="text-align: right;;">1162.35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">8/24/2011</td><td style="text-align: right;;">1177.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">8/25/2011</td><td style="text-align: right;background-color: #FFFF00;;">1159.27</td><td style="text-align: right;;">-1.58%</td><td style="text-align: right;;"></td><td style=";">Highest</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">8/26/2011</td><td style="text-align: right;;">1176.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #92D050;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">8/29/2011</td><td style="text-align: right;;">1210.08</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">8/30/2011</td><td style="text-align: right;;">1212.92</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">8/31/2011</td><td style="text-align: right;;">1218.89</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">9/1/2011</td><td style="text-align: right;background-color: #FFFF00;;">1204.42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">9/2/2011</td><td style="text-align: right;background-color: #FFFF00;;">1173.97</td><td style="text-align: right;;">-4.60%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">9/6/2011</td><td style="text-align: right;background-color: #FFFF00;;">1165.24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">9/7/2011</td><td style="text-align: right;;">1198.62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">9/8/2011</td><td style="text-align: right;background-color: #FFFF00;;">1185.9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">9/9/2011</td><td style="text-align: right;background-color: #FFFF00;;">1154.23</td><td style="text-align: right;;">-3.85%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">9/12/2011</td><td style="text-align: right;;">1162.27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">9/13/2011</td><td style="text-align: right;;">1172.87</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">9/14/2011</td><td style="text-align: right;;">1188.68</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">9/15/2011</td><td style="text-align: right;;">1209.11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">9/16/2011</td><td style="text-align: right;;">1216.01</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">9/19/2011</td><td style="text-align: right;background-color: #FFFF00;;">1204.09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">9/20/2011</td><td style="text-align: right;background-color: #FFFF00;;">1202.09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">9/21/2011</td><td style="text-align: right;background-color: #FFFF00;;">1166.76</td><td style="text-align: right;;">-7.65%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">9/22/2011</td><td style="text-align: right;background-color: #FFFF00;;">1129.56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">9/23/2011</td><td style="text-align: right;;">1136.43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">9/26/2011</td><td style="text-align: right;;">1162.95</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">9/27/2011</td><td style="text-align: right;;">1175.38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">9/28/2011</td><td style="text-align: right;background-color: #FFFF00;;">1151.06</td><td style="text-align: right;;">-2.11%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">9/29/2011</td><td style="text-align: right;;">1160.4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;;">9/30/2011</td><td style="text-align: right;background-color: #FFFF00;;">1131.42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">10/3/2011</td><td style="text-align: right;background-color: #FFFF00;;">1099.23</td><td style="text-align: right;;">-5.56%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;;">10/4/2011</td><td style="text-align: right;;">1123.95</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;;">10/5/2011</td><td style="text-align: right;;">1144.03</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;;">10/6/2011</td><td style="text-align: right;;">1164.97</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;;">10/7/2011</td><td style="text-align: right;background-color: #FFFF00;;">1155.46</td><td style="text-align: right;;">-0.82%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="text-align: right;;">10/10/2011</td><td style="text-align: right;;">1194.89</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="text-align: right;;">10/11/2011</td><td style="text-align: right;;">1195.54</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style="text-align: right;;">10/12/2011</td><td style="text-align: right;;">1207.25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="text-align: right;;">10/13/2011</td><td style="text-align: right;background-color: #FFFF00;;">1203.66</td><td style="text-align: right;;">-0.30%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style="text-align: right;;">10/14/2011</td><td style="text-align: right;;">1224.58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">54</td><td style="text-align: right;;">10/17/2011</td><td style="text-align: right;background-color: #FFFF00;;">1200.86</td><td style="text-align: right;;">-1.98%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">55</td><td style="text-align: right;;">10/18/2011</td><td style="text-align: right;;">1225.38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style="text-align: right;;">10/19/2011</td><td style="text-align: right;background-color: #FFFF00;;">1209.88</td><td style="text-align: right;;">-1.28%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style="text-align: right;;">10/20/2011</td><td style="text-align: right;;">1215.39</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">58</td><td style="text-align: right;;">10/21/2011</td><td style="text-align: right;;">1238.25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">59</td><td style="text-align: right;;">10/24/2011</td><td style="text-align: right;;">1254.19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">60</td><td style="text-align: right;;">10/25/2011</td><td style="text-align: right;background-color: #FFFF00;;">1229.05</td><td style="text-align: right;;">-2.05%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">61</td><td style="text-align: right;;">10/26/2011</td><td style="text-align: right;;">1242</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">62</td><td style="text-align: right;;">10/27/2011</td><td style="text-align: right;;">1284.59</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">63</td><td style="text-align: right;;">10/28/2011</td><td style="text-align: right;;">1285.09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">64</td><td style="text-align: right;;">10/31/2011</td><td style="text-align: right;;">1253.3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">65</td><td style="text-align: right;;">11/1/2011</td><td style="text-align: right;background-color: #FFFF00;;">1218.28</td><td style="text-align: right;;">-2.87%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">66</td><td style="text-align: right;;">11/2/2011</td><td style="text-align: right;;">1237.9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">67</td><td style="text-align: right;;">11/3/2011</td><td style="text-align: right;;">1261.15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">68</td><td style="text-align: right;;">11/4/2011</td><td style="text-align: right;background-color: #FFFF00;;">1253.23</td><td style="text-align: right;;">-0.63%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=(<font color="Blue">B5-B2</font>)/B2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=(<font color="Blue">B7-B6</font>)/B7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">=(<font color="Blue">B11-B10</font>)/B11</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C14</th><td style="text-align:left">=(<font color="Blue">B14-B12</font>)/B14</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C18</th><td style="text-align:left">=(<font color="Blue">B18-B17</font>)/B18</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C24</th><td style="text-align:left">=(<font color="Blue">B25-B22</font>)/B25</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C28</th><td style="text-align:left">=(<font color="Blue">B28-B26</font>)/B28</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C36</th><td style="text-align:left">=(<font color="Blue">B37-B33</font>)/B37</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C41</th><td style="text-align:left">=(<font color="Blue">B41-B40</font>)/B41</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C44</th><td style="text-align:left">=(<font color="Blue">B44-B42</font>)/B44</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C48</th><td style="text-align:left">=(<font color="Blue">B48-B47</font>)/B48</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C52</th><td style="text-align:left">=(<font color="Blue">B52-B51</font>)/B52</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C54</th><td style="text-align:left">=(<font color="Blue">B54-B53</font>)/B54</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C56</th><td style="text-align:left">=(<font color="Blue">B56-B55</font>)/B56</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C60</th><td style="text-align:left">=(<font color="Blue">B60-B59</font>)/B60</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C65</th><td style="text-align:left">=(<font color="Blue">B65-B64</font>)/B65</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C68</th><td style="text-align:left">=(<font color="Blue">B68-B67</font>)/B68</td></tr></tbody></table></td></tr></table><br />

 

[capex]

DonkeyOte,

Thank you, all of the suggestions are working very well including yours, I did try to enter vba code but I think I am missing something, I am amaze with all of you guys coming up with solutions. I am working on to pull start and end dates of drops now. Thank you for taking time.
AD

 

[circledchicken]

Quick question, is there a way to pull start and ene date to adjacent cells of highest drop?

If you were using my convoluted way you could try:

• Biggest Drop (e.g. in cell J1): =MIN(I:I)
• Start Date: =INDEX(G:G,MATCH(J1,I:I,0))
• End Date: =INDEX(F:F,MATCH(J1,I:I,0))

Or maybe just filter the data and then the Start and End dates will show up in column G and F (in the unlikely event that there are multiple minimum points the filter may be useful).

 

[capex]

I am also trying to find following, which is slightly different than previous problem.

Criteria:

1. Calculate drop from highest to lowest.
2. Calculation stops once value goes up again beyond previous high.
3. If value makes previous high and starts dropping new calculation begins.
4. Yellow highlighted area is first drop of -14.66%, lowest value was 1099.23 in B44 and highest was 1260.34 in B2. This calculation stopped when value in series exceeded B2 with B62 at 1284.59.
5. Green highlighted is second drop it only started with value exceeding B2, which was previous high.
6. Find highest peak to trough drop percentage and dates.

Thank you
AD

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">Adj Close</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">8/3/2011</td><td style="text-align: right;background-color: #FFFF00;;">1260.34</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">8/4/2011</td><td style="text-align: right;background-color: #FFFF00;;">1200.07</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">8/5/2011</td><td style="text-align: right;background-color: #FFFF00;;">1199.38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">8/8/2011</td><td style="text-align: right;background-color: #FFFF00;;">1119.46</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">8/9/2011</td><td style="text-align: right;background-color: #FFFF00;;">1172.53</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">8/10/2011</td><td style="text-align: right;background-color: #FFFF00;;">1120.76</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">8/11/2011</td><td style="text-align: right;background-color: #FFFF00;;">1172.64</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8/12/2011</td><td style="text-align: right;background-color: #FFFF00;;">1178.81</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">8/15/2011</td><td style="text-align: right;background-color: #FFFF00;;">1204.49</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8/16/2011</td><td style="text-align: right;background-color: #FFFF00;;">1192.76</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">8/17/2011</td><td style="text-align: right;background-color: #FFFF00;;">1193.89</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">8/18/2011</td><td style="text-align: right;background-color: #FFFF00;;">1140.65</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">8/19/2011</td><td style="text-align: right;background-color: #FFFF00;;">1123.53</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">8/22/2011</td><td style="text-align: right;background-color: #FFFF00;;">1123.82</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">8/23/2011</td><td style="text-align: right;background-color: #FFFF00;;">1162.35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">8/24/2011</td><td style="text-align: right;background-color: #FFFF00;;">1177.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">8/25/2011</td><td style="text-align: right;background-color: #FFFF00;;">1159.27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">8/26/2011</td><td style="text-align: right;background-color: #FFFF00;;">1176.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">8/29/2011</td><td style="text-align: right;background-color: #FFFF00;;">1210.08</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">8/30/2011</td><td style="text-align: right;background-color: #FFFF00;;">1212.92</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">8/31/2011</td><td style="text-align: right;background-color: #FFFF00;;">1218.89</td><td style="text-align: right;;">-14.66%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">9/1/2011</td><td style="text-align: right;background-color: #FFFF00;;">1204.42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">9/2/2011</td><td style="text-align: right;background-color: #FFFF00;;">1173.97</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">9/6/2011</td><td style="text-align: right;background-color: #FFFF00;;">1165.24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">9/7/2011</td><td style="text-align: right;background-color: #FFFF00;;">1198.62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">9/8/2011</td><td style="text-align: right;background-color: #FFFF00;;">1185.9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">9/9/2011</td><td style="text-align: right;background-color: #FFFF00;;">1154.23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">9/12/2011</td><td style="text-align: right;background-color: #FFFF00;;">1162.27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">9/13/2011</td><td style="text-align: right;background-color: #FFFF00;;">1172.87</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">9/14/2011</td><td style="text-align: right;background-color: #FFFF00;;">1188.68</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">9/15/2011</td><td style="text-align: right;background-color: #FFFF00;;">1209.11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">9/16/2011</td><td style="text-align: right;background-color: #FFFF00;;">1216.01</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">9/19/2011</td><td style="text-align: right;background-color: #FFFF00;;">1204.09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">9/20/2011</td><td style="text-align: right;background-color: #FFFF00;;">1202.09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">9/21/2011</td><td style="text-align: right;background-color: #FFFF00;;">1166.76</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">9/22/2011</td><td style="text-align: right;background-color: #FFFF00;;">1129.56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">9/23/2011</td><td style="text-align: right;background-color: #FFFF00;;">1136.43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">9/26/2011</td><td style="text-align: right;background-color: #FFFF00;;">1162.95</td><td style="text-align: right;;"></td><td style="background-color: #FFC000;;">Highest</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">9/27/2011</td><td style="text-align: right;background-color: #FFFF00;;">1175.38</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFC000;;">-14.66%</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">9/28/2011</td><td style="text-align: right;background-color: #FFFF00;;">1151.06</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">9/29/2011</td><td style="text-align: right;background-color: #FFFF00;;">1160.4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;;">9/30/2011</td><td style="text-align: right;background-color: #FFFF00;;">1131.42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">10/3/2011</td><td style="text-align: right;background-color: #FFFF00;;">1099.23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;;">10/4/2011</td><td style="text-align: right;;">1123.95</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;;">10/5/2011</td><td style="text-align: right;;">1144.03</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;;">10/6/2011</td><td style="text-align: right;;">1164.97</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;;">10/7/2011</td><td style="text-align: right;;">1155.46</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="text-align: right;;">10/10/2011</td><td style="text-align: right;;">1194.89</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="text-align: right;;">10/11/2011</td><td style="text-align: right;;">1195.54</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style="text-align: right;;">10/12/2011</td><td style="text-align: right;;">1207.25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="text-align: right;;">10/13/2011</td><td style="text-align: right;;">1203.66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style="text-align: right;;">10/14/2011</td><td style="text-align: right;;">1224.58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">54</td><td style="text-align: right;;">10/17/2011</td><td style="text-align: right;;">1200.86</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">55</td><td style="text-align: right;;">10/18/2011</td><td style="text-align: right;;">1225.38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style="text-align: right;;">10/19/2011</td><td style="text-align: right;;">1209.88</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style="text-align: right;;">10/20/2011</td><td style="text-align: right;;">1215.39</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">58</td><td style="text-align: right;;">10/21/2011</td><td style="text-align: right;;">1238.25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">59</td><td style="text-align: right;;">10/24/2011</td><td style="text-align: right;;">1254.19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">60</td><td style="text-align: right;;">10/25/2011</td><td style="text-align: right;;">1229.05</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">61</td><td style="text-align: right;;">10/26/2011</td><td style="text-align: right;;">1242</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">62</td><td style="text-align: right;;">10/27/2011</td><td style="text-align: right;background-color: #92D050;;">1284.59</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">63</td><td style="text-align: right;;">10/28/2011</td><td style="text-align: right;background-color: #92D050;;">1285.09</td><td style="text-align: right;;">-5.44%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">64</td><td style="text-align: right;;">10/31/2011</td><td style="text-align: right;background-color: #92D050;;">1253.3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">65</td><td style="text-align: right;;">11/1/2011</td><td style="text-align: right;background-color: #92D050;;">1218.28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">66</td><td style="text-align: right;;">11/2/2011</td><td style="text-align: right;;">1237.9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">67</td><td style="text-align: right;;">11/3/2011</td><td style="text-align: right;;">1261.15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">68</td><td style="text-align: right;;">11/4/2011</td><td style="text-align: right;;">1253.23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[capex]

Hi,

It worked, thank you, you have been very helpful regardless of technique, I truly appreciate you taking time to help. I have posted slightly different problem. Thanks.

 

[circledchicken]

Hi again,

Here's another long method - hopefully someone can help you with something more concise:

• Column C: Start Points - In C2 enter =IF(B2>MAX(C$1:C1),B2,"") and drag down to the end
• Column D: Period Max - In D2 enter =IF(AND(B2>=B3,B2>MAX(C$1:C1)),B2,"") and drag down to the end
• Column E: Period - In E2 enter =COUNT(D$2:D2)+IF(COUNT(C2:D2)=1,1,0) and drag down to the end
• Column F: Period Start - In F2 enter =IF(I2<>"",A2,"") and drag down to the end
• Column G: Period End - In G2 enter =IF(F2<>"",IF(ISERROR(MATCH(E2+1,E:E,0)),MAX(A:A),INDEX(A:A,MATCH(E2+1,E:E,0)-1)),"") and drag down to the end
• Column H: Period Min - In H2 enter =IF(E2<>E1,MIN(INDIRECT("B"&MATCH(F2,A:A,0)&":B"&MATCH(G2,A:A,0))),"") and drag down to the end
• Column I: Period Max (in line with min) - In I2 enter =IF(E2<>E1,SUMIF(E:E,E2,D:D),"") and drag down to the end
• Column J: Drop - In J2 enter =IF(I2<>"",(H2-I2)/H2,"") and drag down to the end

Then to get the biggest drop summary:

• Biggest Drop (e.g. in cell K1): =MIN(J:J)
• Start Date: =INDEX(F:F,MATCH(K1,J:J,0))
• End Date: =INDEX(G:G,MATCH(K1,J:J,0))

I think if you drag down as far the formulae as far as you need it should work as you add more data.

 

[Peter_SSs]

Quick question, is there a way to pull start and ene date to adjacent cells of highest drop?

From what I can see, it would be possible that the 'highest drop' could occur more than once. How would you want that situation handled?

Would you need every pair of start/end dates listed, or just the first pair, or just the last pair, or just report that there are multiples, or something else?

I'll have a look at your other questiuon when I have a bit more time.

 

[circledchicken]

• Column J: Drop - In J2 enter =IF(I2<>"",(H2-I2)/H2,"") and drag down to the end

Actually I think in J2 it should be =IF(I2<>"",(H2-I2)/I2,"")

I'm a little confused by the percentages in your example e.g. in the green area I would think you would want (1218.28(Min) - 1285.09(Max))/(1285.09(Max)). In the green area the 1284.59 only signals the start of the new period, but the peak to use in the calculation is the maximum 1285.09?

 

[capex]

HI,

You are right about your observation, peak is 1285.09. I will be trying your suggestion tomorrow. Thank you.

 

[capex]

Hi, Peter,

Would like to know only highest drop start and end date in entire series. Which is -11.18% starts on 8/3 and ends on 8/8/2011. Thank you.

 

[capex]

Quickly tried your solution and worked well, Thank you very much.