Longest Streak Of Visible Cells

[honkin]

I have a formula which calculates longest streak of the number 1 in column AC, but I have just realised that the sheet will quite often be autofiltered

This is the formula

{=MAX(FREQUENCY(IF(AC:AC=1,ROW(AC:AC)),IF(AC:AC<>1,ROW(AC:AC))))}

It is an array formula, but it does not handle the results correctly when the sheet is filtered

Any idea of the change/s required to have it work only on visible cells?

cheers

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=MAX(FREQUENCY(IF(SUBTOTAL(3,OFFSET(AC2:AC100,ROW(AC2:AC100)-ROW(AC2),0,1))>0,IF(AC2:AC100=1,ROW(AC2:AC100))),IF(AC2:AC100<>1,ROW(AC2:AC100))))

...and adjust the range accordingly. I would suggest that you avoid whole column references, since it's very inefficient. Also, to exclude manually hidden rows from the calculation, change SUBTOTAL(3, . . . ) TO SUBTOTAL(103, . . . ).

Hope this helps!

 

[honkin]

cheers Domenic and thanks for your reply

The above formula does seem to accurately calculate the number of consecutive 1s in column AC. I changed subtotal 3 to 103 so it handled manually hidden rows, but still it is not being accurate at all. Here is an example below with the cell I have the formula in showing the consecutive streak of 1s as 1 rather than 2

2021 Latest Results June 2021.xlsb
	AC
14	Finish
15	Position
327	1
947	1
rc-vdw-place-adapted-2021-01-01

This is the full formula now

{=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC16:AC1048576,ROW(AC16:AC1048576)ROW(AC16),0,1))>0,IF(AC16:AC1048576=1,ROW(AC16:AC1048576))),IF(AC16:AC1048576<>1,ROW(AC16:AC1048576))))}

Any thoughts Domenic?

cheers

 

[Domenic]

It looks like you have a typo. You're missing a minus ( - ) sign...

=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC16:AC1048576,ROW(AC16:AC1048576)-ROW(AC16),0,1))>0,IF(AC16:AC1048576=1,ROW(AC16:AC1048576))),IF(AC16:AC1048576<>1,ROW(AC16:AC1048576))))

 

[honkin]

It looks like you have a typo. You're missing a minus ( - ) sign...

=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC16:AC1048576,ROW(AC16:AC1048576)-ROW(AC16),0,1))>0,IF(AC16:AC1048576=1,ROW(AC16:AC1048576))),IF(AC16:AC1048576<>1,ROW(AC16:AC1048576))))

Cheers again Domenic

Strange the minus sign disappeared, as I merely did a copy and past and altered the range.

Ah I know what it was. The minus was there, but when I copied it here, it went to 2 lines, right at the minus. When I backspaced to get it on a single line, I obviously deleted it, but only here on the forum.

Still no good with this code, unfortunately. Here is a sample

2021 Latest Results June 2021.xlsb
	AC
14	Finish
15	Position
19	4
21	2
43	1
124	1
204	1
235	3
327	1
445	1
500	6
503	2
rc-vdw-place-adapted-2021-01-01

The result cell shows the number of consecutive 1s as 1, not 3

Here is the formula

{=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC16:AC1048576,ROW(AC16:AC1048576)-ROW(AC16),0,1))>0,IF(AC16:AC1048576=1,ROW(AC16:AC1048576))),IF(AC16:AC1048576<>1,ROW(AC16:AC1048576))))}

Any thoughts as to what may be stopping it from working correctly?

cheers

 

[Domenic]

Sorry, my mistake, we have to add the same subtotal criteria for the bins array...

=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC16:AC1048576,ROW(AC16:AC1048576)-ROW(AC16),0,1))>0,IF(AC16:AC1048576=1,ROW(AC16:AC1048576))),IF(SUBTOTAL(103,OFFSET(AC16:AC1048576,ROW(AC16:AC1048576)-ROW(AC16),0,1))>0,IF(AC16:AC1048576<>1,ROW(AC16:AC1048576)))))

...confirmed with CONTROL+SHIFT+ENTER.

 

[honkin]

Apologies for the long delay, Domenic. This seems to work very well, so thank you very much

cheers

 

[Domenic]

You're very welcome, cheers!