# Finding highest drop in value

#### capex

##### New Member
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.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Hello, try this:

=MIN(C2:C5000)

#### capex

##### New Member
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.

#### NateO

##### Legend
Hola, that is the formula - give it a shot.

#### capex

##### New Member
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

#### circledchicken

##### Well-known Member
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?

#### capex

##### New Member
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 />

#### circledchicken

##### Well-known Member
Hi,

Are you sure you did =B3-B2 in C3? - The positives/negatives seem to be reversed?

#### capex

##### New Member
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.