Excel Formula Help

[mcgurrgurr]

Hi there,

I am trying to write a formula that will give me the count of the longest streak of numbers in a column. The column has 36 values that are 1's or 0's. I would like the formula to find and output the series that has the longest streak of one's, but that resets if it encounters a 0.

For Example:

1
1
0
1
1
1
0
0
1
0


In this list the output would be 3.

If anyone can help that would be great!

Thanks!

 

[shg]

Confirmed with Ctrl+Shift+Enter:

=MAX(FREQUENCY(IF(A1:A36=1, ROW(A1:A36)), IF(A1:A36<>1, ROW(A1:A36))))

 

[mcgurrgurr]

Thanks for the reply!

I tried putting this is and I am getting an error (#VALUE!)

I think that I understand what your formula is doing, but I think I am running into problems understand the ROW function. I think it may be what is giving me the error message.

 

[shg]

You can't confirm the formula in the usual way with Enter. You MUST press and hold the Ctrl and Shift keys, then press Enter.

 

[mcgurrgurr]

Ah, I understand now. I had never confirmed a statement in such a manner. Thank you so much for clarifying.

If I may, I have a similar question related to the same formula. If I wanted to take a series of numbers in a column and count how many sets of ten consecutive values greater than 0 there are, how would I go about doing so?


for example:

0
[series of 10 consecutive values >0]
0
0
0
0
[series of 10 consecutive values >0]
0
0


The formula would output 2 as the answer


Thanks again! I truly appreciate the help!

 

[shg]

Same idea:

=SUMPRODUCT( --(FREQUENCY(IF(A1:A100>0, ROW(A1:A100)), IF(A1:A100<=0, ROW(A1:A100)))>=10) )