Counting MAX number of consecutive blank cells

[voitoosh]

Hi All,
I have an Excel 2010-related problem.
I need some tips on how to solve this. I have tried some formulas but it only puzzled me :/

I have a row of data, e.g.

WORK WORK WORK WORK __ __ __ __ __ OFF OFF OFF OFF __ __ __ LVE LVE __ WORK WORK WORK. -> this should return 5, as it is the biggest "block" of consecutive blank cells.

I need to calculate what is the MAX number of blank cells in this row (I have tried countifs, MAX etc) but there is probably one long formula to do so. Also, these (blanks) appear in a different places throughout the row (it is basically a roster of a person).

Any ideas how to tackle this without VBA (in which I am rubbish)?

Help much appreciated!

Thanks a lot!

 

[Aladin Akyurek]

Hi All,
I have an Excel 2010-related problem.
I need some tips on how to solve this. I have tried some formulas but it only puzzled me :/

I have a row of data, e.g.

WORK WORK WORK WORK __ __ __ __ __ OFF OFF OFF OFF __ __ __ LVE LVE __ WORK WORK WORK. -> this should return 5, as it is the biggest "block" of consecutive blank cells.

I need to calculate what is the MAX number of blank cells in this row (I have tried countifs, MAX etc) but there is probably one long formula to do so. Also, these (blanks) appear in a different places throughout the row (it is basically a roster of a person).

Any ideas how to tackle this without VBA (in which I am rubbish)?

Help much appreciated!

Thanks a lot!

Control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(A2:K2="",COLUMN(A2:K2)),IF(A2:K2<>"",COLUMN(A2:K2))))

 

[voitoosh]

Ooh fudge! It works!
I have found similar solutions but could never understand the syntax (COLUMN and FREQUENCY formulas are to be repeated).

Thanks a lot Aladin!

 

[Aladin Akyurek]

Ooh fudge! It works!
I have found similar solutions but could never understand the syntax (COLUMN and FREQUENCY formulas are to be repeated).

FREQUENCY(Values,Bins)


where Values are generated with: IF(A2:K2="",COLUMN(A2:K2)). This yields an array with column numbers of the columns equaling a blank, FALSE's otherwise. Something like:


{FALSE,FALSE,FALSE,4,5,6,7,8,FALSE,FALSE,FALSE}

Bins, onto which the foregoing numbers must be mapped, are created with: IF(A2:K2<>"",COLUMN(A2:K2)). This yields in the same way something like:

{1,2,3,FALSE,FALSE,FALSE,FALSE,FALSE,9,10,11}

Obviously, 4,5,6,7,8 all go in the 9 bin, giving a count of 5 for that bin, etc:

{0;0;0;5;0;0;0}

The surrounding MAX picks up that count.

Thanks a lot Aladin!

You are welcome.

 

[voitoosh]

Aladin, Anyone?

Is there a way that there will be a formula that will look at these consecutive blank cells in a row and I will give it a condition that if there is 5 or more consec blanks, it will assign 1 day off?

I almost want it to scan through the row, find blanks and if >=5, assign 1 (or at least the formula will result in number of days off i need to give someone).

Would sumif work? I will probably need an array formula for it.

 

[barry houdini]

Would it be 1 for every run of 5 or more, even 10 or 15? If so try this version

=SUM(IF(FREQUENCY(IF(A2:Z2="",COLUMN(A2:Z2)),IF(A2:Z2<>"",COLUMN(A2:Z2)))>=5,1))

 

[voitoosh]

Would it be 1 for every run of 5 or more, even 10 or 15? If so try this version

=SUM(IF(FREQUENCY(IF(A2:Z2="",COLUMN(A2:Z2)),IF(A2:Z2<>"",COLUMN(A2:Z2)))>=5,1))

Hey Barry,
Thanks for Your help. Well, it will be repeating, as idea is to assume how many days off someone will get. So the formula works (I can change "1" to any number and it will multiply it.

However, I want it almost to think that if it sees
( ) ( ) ( ) ( ) ( )
it will think that the sixth cell will be a Day off. and then it goes again.

Trouble is the pattern does not always follow the rules and it is for someone who is working on a flexible basis. :/

Thanks

 

[barry houdini]

If you want every group of 5 to count 1 (so 8 would count as 1 but 10 would count as 2) then try this version

=SUM(INT(FREQUENCY(IF(A2:Z2="",COLUMN(A2:Z2)),IF(A2:Z2<>"",COLUMN(A2:Z2)))/5))

 

[voitoosh]

Yes, somewhat this, Trouble is that if there is a gap bigger than 5, i should give someone two days off. Gives me a rough result, which is ok, I will try to sass it out from here now.
Thanks Barry!

 

[voitoosh]

IS there a way in which the formula will look at consecutive blank cells and it will shift to the right, and if it finds 5blanks it will assume (1 or 2 days off), once it has found one such "block" of blanks, it will shift to the right etc etc. and until the end of range?

It is almost like a Snake (mobile game) but moves to the right and sums how many days off i will need to give to someone?

Example:
X - Day Working
[] - Blank Day (someone can work or be given a day off).

X X X X X [] [] [] [] [] [] [] [] X X X X X [][][][]XXXX[][][][][][][][][]

So You can see the first "block of blanks" is 8-long, so it would need me to assume 2 days off. next block is <5, so no D/O, next block is 9 blanks, so either one in between or 2 at the end.

My biggest trouble is to make it look at these columns (or: blocks) of blanks and treat them separately from each other and if there is a condition ([]>5, assume days off accordingly). Is that even possible?

Thanks,