# 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"  Register To Reply

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.  Register To Reply

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  Register To Reply

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  Register To Reply

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  Register To Reply

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