Counting MAX number of consecutive blank cells

V

voitoosh

New Member
Joined
May 19, 2013
Messages
32
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!
 
Sort by date Sort by votes
Basically row 4 would be 'scanned' from E to O and the result would be 1 because there is only 1 consecutive blank...

Whereas row 7 would be scanned from F to O and the result would be 2 because there are 2 consecutive blanks....

Based on the row attribute being X or Y (to be added later) if the consecutive blanks are greater than 1 (for X) or 3 (for Y) then that row has been an unbroken pattern within the timeline it has been active....if that makes sense...

In reality the first column can always be E...now I think about it...so the last active row is the only thing that needs to be dynamically driven.

[TABLE="width: 739"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD]Column
[/TD]
[TD]E
[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H
[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Row
[/TD]
[TD]Jan-12[/TD]
[TD]Feb-12[/TD]
[TD]Mar-12[/TD]
[TD]Apr-12
[/TD]
[TD]May-12[/TD]
[TD]Jun-12[/TD]
[TD]Jul-12[/TD]
[TD]Aug-12[/TD]
[TD]Sep-12[/TD]
[TD]Oct-12[/TD]
[TD]Nov-12[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]32[/TD]
[TD]27[/TD]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD]29[/TD]
[TD]7[/TD]
[TD]130[/TD]
[TD]68[/TD]
[TD][/TD]
[TD]127[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]91[/TD]
[TD]126[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]70[/TD]
[TD]144[/TD]
[TD]143[/TD]
[TD]77[/TD]
[TD]106[/TD]
[TD]16[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]128[/TD]
[TD]102[/TD]
[TD]56[/TD]
[TD]8
[/TD]
[TD]93[/TD]
[TD]55[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]144[/TD]
[TD]73[/TD]
[TD]129[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]83[/TD]
[TD]44[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD]60
[/TD]
[TD]55
[/TD]
[TD]55[/TD]
[TD]68[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]16[/TD]
[TD]135[/TD]
[TD]101[/TD]
[TD]97
[/TD]
[TD]93[/TD]
[TD]27[/TD]
[TD]120[/TD]
[TD]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]145[/TD]
[TD]96[/TD]
[TD]131[/TD]
[TD]143
[/TD]
[TD]63[/TD]
[TD]91[/TD]
[TD]137[/TD]
[TD][/TD]
[TD]137[/TD]
[TD]82[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]54[/TD]
[TD]107[/TD]
[TD]25[/TD]
[TD]133[/TD]
[TD][/TD]
[TD]128[/TD]
[TD][/TD]
[TD]49[/TD]
[TD]21[/TD]
[TD]64[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]86[/TD]
[TD][/TD]
[TD]101[/TD]
[TD]36[/TD]
[TD]80[/TD]
[TD]71[/TD]
[TD]78[/TD]
[TD]129[/TD]
[TD]65[/TD]
[TD]118[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]142[/TD]
[TD]132[/TD]
[TD][/TD]
[TD]146[/TD]
[TD]134[/TD]
[TD]126[/TD]
[TD]39[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 676"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Batch21 said:
Basically row 4 would be 'scanned' from E to O and the result would be 1 because there is only 1 consecutive blank...

Whereas row 7 would be scanned from F to O and the result would be 2 because there are 2 consecutive blanks....

Based on the row attribute being X or Y (to be added later) if the consecutive blanks are greater than 1 (for X) or 3 (for Y) then that row has been an unbroken pattern within the timeline it has been active....if that makes sense...

In reality the first column can always be E...now I think about it...so the last active row is the only thing that needs to be dynamically driven.

[TABLE="width: 739"]
<tbody>[TR]
[TD]Column[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Row[/TD]
[TD]Jan-12[/TD]
[TD]Feb-12[/TD]
[TD]Mar-12[/TD]
[TD]Apr-12[/TD]
[TD]May-12[/TD]
[TD]Jun-12[/TD]
[TD]Jul-12[/TD]
[TD]Aug-12[/TD]
[TD]Sep-12[/TD]
[TD]Oct-12[/TD]
[TD]Nov-12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]32[/TD]
[TD]27[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD]29[/TD]
[TD]7[/TD]
[TD]130[/TD]
[TD]68[/TD]
[TD][/TD]
[TD]127[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]91[/TD]
[TD]126[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]70[/TD]
[TD]144[/TD]
[TD]143[/TD]
[TD]77[/TD]
[TD]106[/TD]
[TD]16[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]128[/TD]
[TD]102[/TD]
[TD]56[/TD]
[TD]8[/TD]
[TD]93[/TD]
[TD]55[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]144[/TD]
[TD]73[/TD]
[TD]129[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]83[/TD]
[TD]44[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD]55[/TD]
[TD]55[/TD]
[TD]68[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]16[/TD]
[TD]135[/TD]
[TD]101[/TD]
[TD]97[/TD]
[TD]93[/TD]
[TD]27[/TD]
[TD]120[/TD]
[TD]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]145[/TD]
[TD]96[/TD]
[TD]131[/TD]
[TD]143[/TD]
[TD]63[/TD]
[TD]91[/TD]
[TD]137[/TD]
[TD][/TD]
[TD]137[/TD]
[TD]82[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]54[/TD]
[TD]107[/TD]
[TD]25[/TD]
[TD]133[/TD]
[TD][/TD]
[TD]128[/TD]
[TD][/TD]
[TD]49[/TD]
[TD]21[/TD]
[TD]64[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]86[/TD]
[TD][/TD]
[TD]101[/TD]
[TD]36[/TD]
[TD]80[/TD]
[TD]71[/TD]
[TD]78[/TD]
[TD]129[/TD]
[TD]65[/TD]
[TD]118[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]142[/TD]
[TD]132[/TD]
[TD][/TD]
[TD]146[/TD]
[TD]134[/TD]
[TD]126[/TD]
[TD]39[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 676"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

E fixed, End dynamic.

Define BigNum as referring to:

=9.99999999999999E+307

Control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=MAX(FREQUENCY(IF(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2))="",
     COLUMN(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2))),
     IF(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2))<>"",
     COLUMN(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2))))))
 
Upvote 0
Perfect expect for one issue....if E2 is the only cell with a value, so the combination has only become active this last month...it evaluates to #Value, presumably it's the FREQUENCY Statement rather than the INDEX or COLUMN statments....?
 
Upvote 0
Batch21 said:
Perfect expect for one issue....if E2 is the only cell with a value, so the combination has only become active this last month...it evaluates to #Value, presumably it's the FREQUENCY Statement rather than the INDEX or COLUMN statments....?
Click to expand...

Let's wrap the formula into an IFERROR call...
Rich (BB code): 
=IFERROR(MAX(FREQUENCY(IF(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2))="",
   COLUMN(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2)))),
   IF(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2))<>"",
   COLUMN(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2)))))),0)
Change the 0 in IFERROR to "" if that's more appropriate.
 
Upvote 0
Hi ya, superb.....and.....sorry....

What about if I want to count blanks and zeros the same.....would I need to duplicate the FREQUENCY statment or merely double the parameters in the array selection?

I know it's a nightmare when people keep expanding requirements.....
 
Upvote 0
Batch21 said:
Hi ya, superb.....and.....sorry....

What about if I want to count blanks and zeros the same.....would I need to duplicate the FREQUENCY statment or merely double the parameters in the array selection?
Click to expand...

Control+shift+enter and copy down:
Rich (BB code): 
=IFERROR(MAX(FREQUENCY(IF(1-ISNUMBER(1/E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2))),
   COLUMN(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2)))),
   IF(ISNUMBER(1/E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2))),
   COLUMN(E2:INDEX(E2:Z2,MATCH(BigNum,E2:Z2)))))),0)

I know it's a nightmare when people keep expanding requirements.....
Click to expand...

:rofl:... Quite true;)
 
Upvote 0
Hi there, sorry I'm using this formula...didn't use the dynamic range expansion in the end.

=MAX(FREQUENCY(IF(OFFSET(J3,0,0,1,COUNTA($J$2:$AI$2))="",COLUMN(OFFSET(J3,0,0,1,COUNTA($J$2:$AI$2)))),IF(OFFSET(J3,0,0,1,COUNTA($J$2:$AI$2))<>"",COLUMN(OFFSET(J3,0,0,1,COUNTA($J$2:$AI$2))))))

So it needs to count the MAX number of consecutive blanks and/or zeros, currently it's just counting blanks and zeros are not counted...unfortunately the data I'm using is 'flexible' so sometimes it can display 0 and sometimes "" even though they essentially represent the same NULL value....
 
Upvote 0
Batch21 said:
Hi there, sorry I'm using this formula...didn't use the dynamic range expansion in the end.

=MAX(FREQUENCY(IF(OFFSET(J3,0,0,1,COUNTA($J$2:$AI$2))="",COLUMN(OFFSET(J3,0,0,1,COUNTA($J$2:$AI$2)))),IF(OFFSET(J3,0,0,1,COUNTA($J$2:$AI$2))<>"",COLUMN(OFFSET(J3,0,0,1,COUNTA($J$2:$AI$2))))))

So it needs to count the MAX number of consecutive blanks and/or zeros, currently it's just counting blanks and zeros are not counted...unfortunately the data I'm using is 'flexible' so sometimes it can display 0 and sometimes "" even though they essentially represent the same NULL value....
Click to expand...

OFFSET/COUNTA has the same intent as the one I provided. What is the reason for dismissing the formula already crafted for you?
 
Upvote 0
Hiii,

I am facing same type of challenge where i have to count maximum no of consecutive filled cells(Instead of blank).

Please help
Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
889
felixstraube
F
J
  • Question Question
How many times has a number been below zero consecutively formula?
Replies
1
Views
138
Peter_SSs
Peter_SSs
I
  • Solved
Formula to count consecutive zeros at the end of a row
Replies
7
Views
394
Peter_SSs
Peter_SSs
Y
  • Question Question
How to count number of consecutive wet days in the a rainfall table?
2
Replies
19
Views
1K
felixstraube
F
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
655
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top