# Find the cell reference of consecutive largest negative/positive numbers

1. ## Find the cell reference of consecutive largest negative/positive numbers

Dear Members

I am trying a make formulas in the sheet attached following is my sheet's data
I have different positive and negative numbers in column B of the the sheet
In column C values are cumulative from column B

Now What i want to do is

1. Maximum number of consecutive +ve Numbers (Streak) in Column B
2. Sum of Max Consecutive +ve numbers
3. Cell reference of starting maximum Positive consecutive numbers (Streak) in column B whose numbers are calculated in column "F"
4. Maximum number of consecutive -ve Numbers (Streak) in Column B
5. Sum of Max Consecutive -ve numbers
6. Cell reference of starting maximum Positive consecutive numbers (Streak) in column B whose numbers are calculated in column "I"

2. ## Re: Find the cell reference of consecutive largest negative/positive numbers

Welcome to the forum.

This reads rather more like a brief than a request for help, so to reassure us that you are not just expecting us to give you a solution to all 6 issues, tell us or show us what you have tried in each case, and explain why it isn't quite working for you.

The aim here is to coach you. It's not a case of you giving us a list of things you want doing and expecting us to sort it out for you. The nature of the forum is meant to be one of collaboration.

3. ## Re: Find the cell reference of consecutive largest negative/positive numbers

Thank you AliGW for response

i have tried

=MAX(FREQUENCY(IF(B2:B2001>0,ROW(B2:B2001)),IF(B2:B2001<=0,ROW(B2:B2001)))) Ctrl+Shift+enter for 1st & 4th Case
=SUM(OFFSET(A1,-1+MATCH(TRUE,COUNTIF(OFFSET(A1,ROW(INDIRECT("1:"&ROWS(A1:A11)-B2+1))-1,0,B2,1),"<0")=B2,0),0,B2,1)) Ctrl+Shift+enter for 2nd & 4th Case
and both worked but i have no idea of getting the cell reference of or sr numbers of these values as i asked in sr 3 & 6 in post

4. ## Re: Find the cell reference of consecutive largest negative/positive numbers

In F2

=MAX(FREQUENCY(IF(\$B\$2:\$B\$2001>0,ROW(\$B\$2:\$B\$2001)),IF(\$B\$2:\$B\$2001<=0,ROW(\$B\$2:\$B\$2001))))

with Ctrl+Shift+Enter

in G2

=SUM(OFFSET(B2,MATCH(MAX(I2:I2001),I2:I2001,0)-\$F\$2,0,MAX(I2:I2001)))

in H2

="B" &MATCH(\$F\$2,\$I\$1:\$I\$2001,0)-\$F\$2+1

in I2 and copy down ("Helper" column)

=IF(B2>0,I1+1,0)

Similar for negative values

5. ## Re: Find the cell reference of consecutive largest negative/positive numbers

Thanks John Topley, this was what i was looking for.
Have a great day Thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)