Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi everybody

    I have a small problem that I need to solve if anybody could help me please, I would be greatfull.

    I have long lists from which I need to extract the longest run of positive and negative numbers.

    I attach a small work book to explain better.

    Any help would be appreciated.

    Regards
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='kobusl' post='790921' date='27-Aug-2009 06:20']Hi everybody

    I have a small problem that I need to solve if anybody could help me please, I would be greatfull.

    I have long lists from which I need to extract the longest run of positive and negative numbers.

    I attach a small work book to explain better.

    Any help would be appreciated.

    Regards[/quote]
    The attached file achieves the intended result. Note that it provides the sum of the first instance in the event of two runs of the same length.
    Attached Files Attached Files
    Regards
    Don

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached version; the approach was inspired by sdckapr's attachment to Post 721227.

    I added auxiliary formulas to calculate the length of a "run" in column C.

    In B35 as an array formula: =MAX(IF(B3:B33>0,C3:C33))
    In B36 as an array formula: =SUM(OFFSET(B1,MAX(ROW(C3:C33)*(B3:B33>0)*(C3:C33= B35))-B35,0,B35,1))
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='790941' date='27-Aug-2009 08:06']See the attached version; the approach was inspired by sdckapr's attachment to Post 721227.

    I added auxiliary formulas to calculate the length of a "run" in column C.

    In B35 as an array formula: =MAX(IF(B3:B33>0,C3:C33))
    In B36 as an array formula: =SUM(OFFSET(B1,MAX(ROW(C3:C33)*(B3:B33>0)*(C3:C33= B35))-B35,0,B35,1))[/quote]
    Thank you for that Hans. I will study your solution to strengthen my array capability.
    Regards
    Don

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for both replies.

    Obviously the trick is in adding the extra column(s), I cracked my brain for the last few days.

    Thank you again for the fast replies, I really appreciate it

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='wdwells' post='790943' date='27-Aug-2009 14:11']Thank you for that Hans. I will study your solution to strengthen my array capability.[/quote]
    I like your idea of using negative numbers for the negative runs - it simplifies the calculation of the longest runs.

  7. #7
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I was wondering if it is possible to also count the runs per month and how you would specify the parameters for that?

    I again include a workbook for easy reference.

    Regards
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached. I added yet another auxiliary column, to calculate the run lengths within each month separately.
    Attached Files Attached Files

  9. #9
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans

    Thank you very much, once again I stand amazed at your speed and briliance.

    Just Briliant the solutions

    Regards

Posting Permissions

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