Finding highest drop in value

capex

New Member
Joined
Apr 18, 2009
Messages
44
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 />
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi, NateO,

Thank you for the reply. I have found value in column C manually, I want to automate this process using formula. Thank you.
AD
 
Upvote 0
I understand, your formula does work (but it is secondary) I am trying to accomplish following:

In column B I want to automatically find decreased value from previous day as highlighted, I have manually found these but I would like to automate finding this reduced values.

Example: B2, 1260.34 to B3, 1200.07(there was a drop from B2 to B3, that started the series) and continued to drop till B5, 1119.46. B6 was higher that B5 so it stopped these three cell series.

1. It started again at B7 because it was lower than B6. It goes on, how do I automate using formula to identify this highlighted drops in column B?,

2. Once value or series is identified(currently highlighted in example) how can it be calculated automatically in percentage drop in column C?

3. After finding percentage in column C your formula can be used to find highest drop.

Thanks
AD
 
Upvote 0
Hi,

This is probably not the most elegant method but its an option. Using a few helper columns you can try the following:

  • Column C: Fall - In C3 (leave C2 blank) enter =B3-B2 and drag down to the end
  • Column D: Cumulative Fall - In D2 enter =IF(SIGN(C2)=-1,SUM(D1+C2)) and drag down to the end
  • Column E: Fall Start - In E2 enter =IF(AND(D2=FALSE,SIGN(D3)=-1),A2,"") and drag down to the end
  • Column F: Fall End - In F2 enter =IF(AND(SIGN(D2)=-1,D3=FALSE),A2,"") and drag down to the end
  • Column G: Fall Start (in line with Fall End) - In G2 enter =IF(F2<>"",MAX($E$2:E2),"") and drag down to the end
  • Column H: Fall Start Value - In H2 enter =IF(F2<>"",INDEX($B$2:$B$68,MATCH(G2,$A$2:$A$68,0)),"") and drag down to the end
  • Column I: Drop % - In I2 enter =IF(F2<>"",D2/H2,"") and drag down to the end

You can then use NateO's MIN formula or filter column I to get the minimum and F and G will tell you the dates it applies between.

Note: I think the Column C formulas in your post seem a little inconsistent e.g. C7 should be =(B7-B6)/B6 not =(B7-B6)/B7 - unless I'm missing something?
 
Upvote 0
Hi,

I tried that but results are different, it should be -11.18%, currently it shows -7.47%. You are right about formulas in column C, I just used them so I don't have to calculate manually. Basic idea is to find drawdowns in series and look for the highest one.

If I start out with $1000 and after five days it becomes 800, on sixth day it starts going up again and ends at 900 and if it drops again to 600, so maximum pull back was 300 when comparing two drops, this is what i am trying to find in market index such as s&p 500.

Thank you for helping, here is how it looks with your suggestion.

<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 /><col /><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><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</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=";">Fall</td><td style=";">Cfall</td><td style=";">FS</td><td style=";">FE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">60.27</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">0.69</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">79.92</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40763</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-53.07</td><td style="text-align: right;;">-53.07</td><td style=";"></td><td style="text-align: right;;">40764</td><td style="text-align: right;;">40763</td><td style="text-align: right;;">1119.46</td><td style="text-align: right;;">-4.74%</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;;">51.77</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40765</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-51.88</td><td style="text-align: right;;">-51.88</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-6.17</td><td style="text-align: right;;">-58.05</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-25.68</td><td style="text-align: right;;">-83.73</td><td style=";"></td><td style="text-align: right;;">40770</td><td style="text-align: right;;">40765</td><td style="text-align: right;;">1120.76</td><td style="text-align: right;;">-7.47%</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;;">11.73</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40771</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-1.13</td><td style="text-align: right;;">-1.13</td><td style=";"></td><td style="text-align: right;;">40772</td><td style="text-align: right;;">40771</td><td style="text-align: right;;">1192.76</td><td style="text-align: right;;">-0.09%</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;;">53.24</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">17.12</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40774</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-0.29</td><td style="text-align: right;;">-0.29</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-38.53</td><td style="text-align: right;;">-38.82</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-15.25</td><td style="text-align: right;;">-54.07</td><td style=";"></td><td style="text-align: right;;">40779</td><td style="text-align: right;;">40774</td><td style="text-align: right;;">1123.53</td><td style="text-align: right;;">-4.81%</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;;">18.33</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40780</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">1176.8</td><td style="text-align: right;;">-17.53</td><td style="text-align: right;;">-17.53</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">1210.08</td><td style="text-align: right;;">-33.28</td><td style="text-align: right;;">-50.81</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-2.84</td><td style="text-align: right;;">-53.65</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-5.97</td><td style="text-align: right;;">-59.62</td><td style=";"></td><td style="text-align: right;;">40786</td><td style="text-align: right;;">40780</td><td style="text-align: right;;">1159.27</td><td style="text-align: right;;">-5.14%</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;;">14.47</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">30.45</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">8.73</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40792</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-33.38</td><td style="text-align: right;;">-33.38</td><td style=";"></td><td style="text-align: right;;">40793</td><td style="text-align: right;;">40792</td><td style="text-align: right;;">1165.24</td><td style="text-align: right;;">-2.86%</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;;">12.72</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">31.67</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40795</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-8.04</td><td style="text-align: right;;">-8.04</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-10.6</td><td style="text-align: right;;">-18.64</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-15.81</td><td style="text-align: right;;">-34.45</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-20.43</td><td style="text-align: right;;">-54.88</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="background-color: #92D050;;">Highest</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;;">-6.9</td><td style="text-align: right;;">-61.78</td><td style=";"></td><td style="text-align: right;;">40802</td><td style="text-align: right;;">40795</td><td style="text-align: right;;">1154.23</td><td style="text-align: right;;">-5.35%</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #92D050;;">-7.47%</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;;">11.92</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">2</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">35.33</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">37.2</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40808</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-6.87</td><td style="text-align: right;;">-6.87</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-26.52</td><td style="text-align: right;;">-33.39</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-12.43</td><td style="text-align: right;;">-45.82</td><td style=";"></td><td style="text-align: right;;">40813</td><td style="text-align: right;;">40808</td><td style="text-align: right;;">1129.56</td><td style="text-align: right;;">-4.06%</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;;">24.32</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40814</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-9.34</td><td style="text-align: right;;">-9.34</td><td style=";"></td><td style="text-align: right;;">40815</td><td style="text-align: right;;">40814</td><td style="text-align: right;;">1151.06</td><td style="text-align: right;;">-0.81%</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;;">28.98</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">32.19</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40819</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-24.72</td><td style="text-align: right;;">-24.72</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-20.08</td><td style="text-align: right;;">-44.8</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-20.94</td><td style="text-align: right;;">-65.74</td><td style=";"></td><td style="text-align: right;;">40822</td><td style="text-align: right;;">40819</td><td style="text-align: right;;">1099.23</td><td style="text-align: right;;">-5.98%</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;;">9.51</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40823</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-39.43</td><td style="text-align: right;;">-39.43</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-0.65</td><td style="text-align: right;;">-40.08</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-11.71</td><td style="text-align: right;;">-51.79</td><td style=";"></td><td style="text-align: right;;">40828</td><td style="text-align: right;;">40823</td><td style="text-align: right;;">1155.46</td><td style="text-align: right;;">-4.48%</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;;">3.59</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40829</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-20.92</td><td style="text-align: right;;">-20.92</td><td style=";"></td><td style="text-align: right;;">40830</td><td style="text-align: right;;">40829</td><td style="text-align: right;;">1203.66</td><td style="text-align: right;;">-1.74%</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;;">23.72</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40833</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-24.52</td><td style="text-align: right;;">-24.52</td><td style=";"></td><td style="text-align: right;;">40834</td><td style="text-align: right;;">40833</td><td style="text-align: right;;">1200.86</td><td style="text-align: right;;">-2.04%</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;;">15.5</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40835</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-5.51</td><td style="text-align: right;;">-5.51</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-22.86</td><td style="text-align: right;;">-28.37</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-15.94</td><td style="text-align: right;;">-44.31</td><td style=";"></td><td style="text-align: right;;">40840</td><td style="text-align: right;;">40835</td><td style="text-align: right;;">1209.88</td><td style="text-align: right;;">-3.66%</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;;">25.14</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40841</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-12.95</td><td style="text-align: right;;">-12.95</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-42.59</td><td style="text-align: right;;">-55.54</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-0.5</td><td style="text-align: right;;">-56.04</td><td style=";"></td><td style="text-align: right;;">40844</td><td style="text-align: right;;">40841</td><td style="text-align: right;;">1229.05</td><td style="text-align: right;;">-4.56%</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;;">31.79</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">35.02</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">40848</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-19.62</td><td style="text-align: right;;">-19.62</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></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;;">-23.25</td><td style="text-align: right;;">-42.87</td><td style=";"></td><td style="text-align: right;;">40850</td><td style="text-align: right;;">40848</td><td style="text-align: right;;">1218.28</td><td style="text-align: right;;">-3.52%</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;;">7.92</td><td style="text-align: right;;">FALSE</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width: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 (2)</p><br /><br />
 
Upvote 0
You are correct, I did made an error there, fixed it and got correct result, as more market data comes in these sheet will continue to find maximum drop? If answer is yes, you have solved big problem for me, thank you very much.

Can you please guide me on how to find those dates? Thank you.

AD
 
Upvote 0
Please ignore my date question, it was self explanatory. Thank you for all your help. AD
 
Upvote 0

Forum statistics

Threads
1,222,011
Messages
6,163,385
Members
451,834
Latest member
tomtownson

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
Back
Top