Largest non-blank sequence in row or column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David - 11 Mar 2008 22:57 GMT

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.

Reply to this Message

Per Erik Midtrød - 11 Mar 2008 23:17 GMT

> I'm trying to create a formula which will count the largest number of
> sequentially non-blank cells in a column or row.
[quoted text clipped - 8 lines]
> 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

Reply to this Message

T. Valko - 11 Mar 2008 23:21 GMT

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)

Signature

Biff
Microsoft Excel MVP

Reply to this Message

David - 11 Mar 2008 23:58 GMT

> With your data in row 1, try this array formula** :
>
[quoted text clipped - 19 lines]
> > particular task in a row. I update the spreadsheet daily and put a "y"
> > in the row if I do that task.

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))))

Reply to this Message

T. Valko - 12 Mar 2008 02:20 GMT

>> With your data in row 1, try this array formula** :
>>
[quoted text clipped - 26 lines]
> =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))))

Signature

Biff
Microsoft Excel MVP

Reply to this Message

Rate this thread: