Counting MAX number of consecutive blank cells

voitoosh · Jun 11, 2013

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!

Batch21 · Mar 13, 2014

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.

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

Aladin Akyurek · Mar 13, 2014

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.

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

<tbody>
</tbody>

<tbody>
</tbody>

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

Batch21 · Mar 14, 2014

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....?

Aladin Akyurek · Mar 14, 2014

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....?

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.

Batch21 · Mar 17, 2014

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

Aladin Akyurek · Mar 17, 2014

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?

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

... Quite true

Batch21 · Mar 17, 2014

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

Aladin Akyurek · Mar 17, 2014

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

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

SandeepKaushik · May 10, 2014

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

Aladin Akyurek · May 10, 2014

SandeepKaushik said:
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

If post #22 does not answer your question, would you post a small sample along with the expected result?

Counting MAX number of consecutive blank cells

voitoosh

New Member

Batch21

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

Batch21

New Member

Aladin Akyurek

MrExcel MVP

Batch21

New Member

Aladin Akyurek

MrExcel MVP

Batch21

New Member

Aladin Akyurek

MrExcel MVP

SandeepKaushik

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Counting MAX number of consecutive blank cells

New Member

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock