Counting the largest number of consecutive 1's in a column
"DavidS" skrev i en meddelelse
...
Hello, I have a column with 1000+ rows, each row having a 0 or 1. I'm
looking for a formula that will give me the highest number of consecutive
1's in the entire column. Just in case I didn't explain this correctly,
here is an example with a column fragment laid sideways:
0101100001111000111110010101. In this example the formula was produce a
result of 5. I'm not sure if this is possible. Thanks for your help, David
David
For a generic formula, which will look for any number or text entered in F1,
this array formula will do the job:
=MAX(FREQUENCY(IF(A1:A1000=F1,COUNTIF(OFFSET(A1,,, ROW(INDIRECT("1:"&ROWS(A1:A1000)))),"<"&F1)),ROW( INDIRECT("1:"&ROWS(A1:A1000)))-1))
To be confirmed with <Shift<Ctrl<Enter, also if edited later.
--
Best regards
Leo Heuser
Followup to newsgroup only please.
|