# Thread: Count longest run of pos/neg numbers Excel 2003

1. 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

2. [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.

3. 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))

4. [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.

5. 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. [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. 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

8. See the attached. I added yet another auxiliary column, to calculate the run lengths within each month separately.

9. 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
•