Page 1 of 5 123 ... LastLast
Results 1 to 10 of 46

Thread: Finding highest drop in value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Finding highest drop in value

    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


    Excel 2007
    ABCDE
    1DateAdj Close
    28/3/20111260.34
    38/4/20111200.07
    48/5/20111199.38-11.18%
    58/8/20111119.46
    68/9/20111172.53
    78/10/20111120.76-4.62%
    88/11/20111172.64
    98/12/20111178.81
    108/15/20111204.49
    118/16/20111192.76-0.98%
    128/17/20111193.89
    138/18/20111140.65
    148/19/20111123.53-6.26%
    158/22/20111123.82
    168/23/20111162.35
    178/24/20111177.6
    188/25/20111159.27-1.58%Highest
    198/26/20111176.8??
    208/29/20111210.08
    218/30/20111212.92
    228/31/20111218.89
    239/1/20111204.42
    249/2/20111173.97-4.60%
    259/6/20111165.24
    269/7/20111198.62
    279/8/20111185.9
    289/9/20111154.23-3.85%
    299/12/20111162.27
    309/13/20111172.87
    319/14/20111188.68
    329/15/20111209.11
    339/16/20111216.01
    349/19/20111204.09
    359/20/20111202.09
    369/21/20111166.76-7.65%
    379/22/20111129.56
    389/23/20111136.43
    399/26/20111162.95
    409/27/20111175.38
    419/28/20111151.06-2.11%
    429/29/20111160.4
    439/30/20111131.42
    4410/3/20111099.23-5.56%
    4510/4/20111123.95
    4610/5/20111144.03
    4710/6/20111164.97
    4810/7/20111155.46-0.82%
    4910/10/20111194.89
    5010/11/20111195.54
    5110/12/20111207.25
    5210/13/20111203.66-0.30%
    5310/14/20111224.58
    5410/17/20111200.86-1.98%
    5510/18/20111225.38
    5610/19/20111209.88-1.28%
    5710/20/20111215.39
    5810/21/20111238.25
    5910/24/20111254.19
    6010/25/20111229.05-2.05%
    6110/26/20111242
    6210/27/20111284.59
    6310/28/20111285.09
    6410/31/20111253.3
    6511/1/20111218.28-2.87%
    6611/2/20111237.9
    6711/3/20111261.15
    6811/4/20111253.23-0.63%

    Sheet1



    Worksheet Formulas
    CellFormula
    C4=(B5-B2)/B2
    C7=(B7-B6)/B7
    C11=(B11-B10)/B11
    C14=(B14-B12)/B14
    C18=(B18-B17)/B18
    C24=(B25-B22)/B25
    C28=(B28-B26)/B28
    C36=(B37-B33)/B37
    C41=(B41-B40)/B41
    C44=(B44-B42)/B44
    C48=(B48-B47)/B48
    C52=(B52-B51)/B52
    C54=(B54-B53)/B54
    C56=(B56-B55)/B56
    C60=(B60-B59)/B60
    C65=(B65-B64)/B65
    C68=(B68-B67)/B68


  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

    Hello, try this:

    =MIN(C2:C5000)

  3. #3
    New Member
    Join Date
    Apr 2009
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

    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

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

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

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

    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

  6. #6
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

    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?

  7. #7
    New Member
    Join Date
    Apr 2009
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

    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.

    Excel 2007
    ABCDEFGHIJK
    1DateAdj CloseFallCfallFSFE
    28/3/20111260.34FALSE
    38/4/20111200.0760.27FALSE
    48/5/20111199.380.69FALSE
    58/8/20111119.4679.92FALSE40763
    68/9/20111172.53-53.07-53.0740764407631119.46-4.74%
    78/10/20111120.7651.77FALSE40765
    88/11/20111172.64-51.88-51.88
    98/12/20111178.81-6.17-58.05
    108/15/20111204.49-25.68-83.7340770407651120.76-7.47%
    118/16/20111192.7611.73FALSE40771
    128/17/20111193.89-1.13-1.1340772407711192.76-0.09%
    138/18/20111140.6553.24FALSE
    148/19/20111123.5317.12FALSE40774
    158/22/20111123.82-0.29-0.29
    168/23/20111162.35-38.53-38.82
    178/24/20111177.6-15.25-54.0740779407741123.53-4.81%
    188/25/20111159.2718.33FALSE40780
    198/26/20111176.8-17.53-17.53
    208/29/20111210.08-33.28-50.81
    218/30/20111212.92-2.84-53.65
    228/31/20111218.89-5.97-59.6240786407801159.27-5.14%
    239/1/20111204.4214.47FALSE
    249/2/20111173.9730.45FALSE
    259/6/20111165.248.73FALSE40792
    269/7/20111198.62-33.38-33.3840793407921165.24-2.86%
    279/8/20111185.912.72FALSE
    289/9/20111154.2331.67FALSE40795
    299/12/20111162.27-8.04-8.04
    309/13/20111172.87-10.6-18.64
    319/14/20111188.68-15.81-34.45
    329/15/20111209.11-20.43-54.88Highest
    339/16/20111216.01-6.9-61.7840802407951154.23-5.35%-7.47%
    349/19/20111204.0911.92FALSE
    359/20/20111202.092FALSE
    369/21/20111166.7635.33FALSE
    379/22/20111129.5637.2FALSE40808
    389/23/20111136.43-6.87-6.87
    399/26/20111162.95-26.52-33.39
    409/27/20111175.38-12.43-45.8240813408081129.56-4.06%
    419/28/20111151.0624.32FALSE40814
    429/29/20111160.4-9.34-9.3440815408141151.06-0.81%
    439/30/20111131.4228.98FALSE
    4410/3/20111099.2332.19FALSE40819
    4510/4/20111123.95-24.72-24.72
    4610/5/20111144.03-20.08-44.8
    4710/6/20111164.97-20.94-65.7440822408191099.23-5.98%
    4810/7/20111155.469.51FALSE40823
    4910/10/20111194.89-39.43-39.43
    5010/11/20111195.54-0.65-40.08
    5110/12/20111207.25-11.71-51.7940828408231155.46-4.48%
    5210/13/20111203.663.59FALSE40829
    5310/14/20111224.58-20.92-20.9240830408291203.66-1.74%
    5410/17/20111200.8623.72FALSE40833
    5510/18/20111225.38-24.52-24.5240834408331200.86-2.04%
    5610/19/20111209.8815.5FALSE40835
    5710/20/20111215.39-5.51-5.51
    5810/21/20111238.25-22.86-28.37
    5910/24/20111254.19-15.94-44.3140840408351209.88-3.66%
    6010/25/20111229.0525.14FALSE40841
    6110/26/20111242-12.95-12.95
    6210/27/20111284.59-42.59-55.54
    6310/28/20111285.09-0.5-56.0440844408411229.05-4.56%
    6410/31/20111253.331.79FALSE
    6511/1/20111218.2835.02FALSE40848
    6611/2/20111237.9-19.62-19.62
    6711/3/20111261.15-23.25-42.8740850408481218.28-3.52%
    6811/4/20111253.237.92FALSE

    Sheet1 (2)




  8. #8
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

    Hi,

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

  9. #9
    New Member
    Join Date
    Apr 2009
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

    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

  10. #10
    New Member
    Join Date
    Apr 2009
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding highest drop in value

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •