Largest non-blank sequence in row or column

[David]

I'm trying to create a formula which will count the largest number of
sequentially non-blank cells in a column or row.

e.g. if the row had the following columns:
|y|y|y|y| | |y|y|y|y|y|y| |y|y|y|y|y|

the value would be 6 because it is the largest number of "y" values
next to each other. Any Ideas please let me know.

I'm using the formula to work out the longest number of days I do a
particular task in a row. I update the spreadsheet daily and put a "y"
in the row if I do that task.

 

[Per Erik Midtrød]

I'm trying to create a formula which will count the largest number of
sequentially non-blank cells in a column or row.

e.g. if the row had the following columns:
|y|y|y|y| | |y|y|y|y|y|y| |y|y|y|y|y|

the value would be 6 because it is the largest number of "y" values
next to each other. Any Ideas please let me know.

I'm using the formula to work out the longest number of days I do a
particular task in a row. I update the spreadsheet daily and put a "y"
in the row if I do that task.

You could use an extra row, if your date is in A1 to R1 or something
put the number 1 in A2 and this formula in A2:R2: IF(B1="y";A2+1;0)
Then use the MAX-function to return the biggest number. I am pretty
sure that someone brighter than me comes up with a solution with no
extra columns soon...


Per Erik

 

[T. Valko]

With your data in row 1, try this array formula** :

=MAX(FREQUENCY(IF(1:1="y",COLUMN(1:1)),IF(1:1<>"Y",COLUMN(1:1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[David]

With your data in row 1, try this array formula** :

=MAX(FREQUENCY(IF(1:1="y",COLUMN(1:1)),IF(1:1<>"Y",COLUMN(1:1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Both of those suggestions work perfectly. I've modified the array
suggestion so that it works on no blank cells and references the
column below the value. Thanks so much for your help.

=MAX(FREQUENCY(IF(NOT(ISBLANK(B$4:B$1000)),ROW(B$4:B
$1000)),IF(ISBLANK(B$4:B$1000),ROW(B$4:B$1000))))

 

[T. Valko]

Both of those suggestions work perfectly. I've modified the array
suggestion so that it works on no blank cells and references the
column below the value. Thanks so much for your help.

=MAX(FREQUENCY(IF(NOT(ISBLANK(B$4:B$1000)),ROW(B$4:B
$1000)),IF(ISBLANK(B$4:B$1000),ROW(B$4:B$1000))))

If the "blank" cells are *empty* you can reduce that to:

=MAX(FREQUENCY(IF(B$4:B$1000<>"",ROW(B$4:B$1000)),IF(B$4:B$1000="",ROW(B$4:B$1000))))