Formula to count maximum consecutive values with a zero resulting in count restarting and count the occurences of the maximum consective values

Question

I found a formula in the forum to complete the values as shown within the question below using formula=MAX(FREQUENCY(IF($A2:$F2=1,COLUMN($A2:$F2)),IF($A2:$F2<>1,COLUMN($A2:$F2))))

However I need to count the occurrences the maximum frequency produces. Screenshot below shows my maximum frequency is three (3) zeros(0) and the formula above results 3 which is great! But I need a formula to also count the number of times this occurs to give me a result of 2, that shows the maximum occurred 2 times in my array of data within that row.

21/12/2019	22/12/2019	23/12/2019	24/12/2019	25/12/2019	26/12/2019	27/12/2019	28/12/2019	29/12/2019	30/12/2019	31/12/2019
0	0	0	1	0	0	0	1	1	0	0

Thank you,

Question in Forum below: ( did not paste well so it's a bit separated)

Formula to count maximum consecutive values with a zero resulting in count restarting

I have a spreadsheet that I'm trying to figure out a formula for, but I'm stuck.  My data needs to be calculated across rows.  I'm trying to calculate the maxium consecutive times the number 1 comes up on each row.  The catch is that the count needs to restart at 0 anytime a 0 is encountered in the row.  The columns all represent weeks of the year, so there will be 52 columns by the end of the year to tabulate.  The original spreadsheet actually has text, but I thought it might be easier to use a formula that had number values, so I had this spreadsheet replace the original text with a "0" for the false condition and a "1" for the true condition.  That leaves me with a spreadsheet that looks like this:

So line 2 and 3 should obviously return values of 0 and lines 4 and 6 should return values of 6.  In line 5, the count has to start over when the 0's are encountered, so that line should return a value of 3.  Line 7 should return a value of 4.  Line 10 is highlighted because it's the one I've been experimenting with the most right now and should return a value of 5.  Line 11 can return a value of either 1 or 0 since the maximum number of consecutive 1's is 1, but the last value is 0, resetting the count. 

Thanks for any help!!

This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.

• Subscribe
• Subscribe to RSS feed

Answer

Amit Tandon

Refer below image:

Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell H2 & copy down:

=MAX(FREQUENCY(IF($A2:$F2=1,COLUMN($A2:$F2)),IF($A2:$F2<>1,COLUMN($A2:$F2))))

Just update the range per your requirement.

Regards,

Amit Tandon

www.globaliconnect.com

Answer 1

W2:  {=MAX(FREQUENCY(IF($A2:$U2=1,COLUMN($A2:$U2)),IF($A2:$U2<>1,COLUMN($A2:$U2))))}

X2:  =IFERROR((LEN(TEXTJOIN("",1,A2:U2))-LEN(SUBSTITUTE(TEXTJOIN("",1,A2:U2),REPT("1",W2),"")))/LEN(REPT("1",W2)),0)

Answer 2

Thank you very much! This solved my issue:)