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!

 

[nnklem]

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!

I am looking to do this exact same thing except with one added hurdle. I do not want to start counting blank cells until after it finds the first cell that contains a value.

Example:
_ _ _ _ _ _ 1 _ _ _ 1 _ _ 1 _ 1

I need the formula to return a value of 3 and not 6. I hope this make sense. Also, if you can help me format as UDF, that would be the ultimate.

Any help is greatly appreciated.

 

[Aladin Akyurek]

I am looking to do this exact same thing except with one added hurdle. I do not want to start counting blank cells until after it finds the first cell that contains a value.

Example:
_ _ _ _ _ _ 1 _ _ _ 1 _ _ 1 _ 1

I need the formula to return a value of 3 and not 6. I hope this make sense. Also, if you can help me format as UDF, that would be the ultimate.

Any help is greatly appreciated.

Let A2:P2 house the relevant data.

A4, control+shift+enter, not just enter:

=MATCH(TRUE,$A$2:$P$2<>"",0)-1

B4, control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(INDEX(A2:P2,A4):P2="",COLUMN(INDEX(A2:P2,A4):P2)),
  IF(INDEX(A2:P2,A4):P2<>"",COLUMN(INDEX(A2:P2,A4):P2))))

Note that the main formula treats an empty cell or blank cell (i.e., a cell with "") the same way.

 

[nnklem]

Let A2:P2 house the relevant data.

A4, control+shift+enter, not just enter:

=MATCH(TRUE,$A$2:$P$2<>"",0)-1

B4, control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(INDEX(A2:P2,A4):P2="",COLUMN(INDEX(A2:P2,A4):P2)),
  IF(INDEX(A2:P2,A4):P2<>"",COLUMN(INDEX(A2:P2,A4):P2))))

Note that the main formula treats an empty cell or blank cell (i.e., a cell with "") the same way.

This is VERY close to working for me. The only problem I see is if I have a row of data like this:

_ _ _ _ _ 1 1 1 1

Your formulas return a value of "1" when it should be ZERO because there are no gaps after the first cell that contains data. Any idea what is causing this? Also, can this be put into a UDF at all?

 

[Aladin Akyurek]

This is VERY close to working for me. The only problem I see is if I have a row of data like this:

_ _ _ _ _ 1 1 1 1

Your formulas return a value of "1" when it should be ZERO because there are no gaps after the first cell that contains data. Any idea what is causing this?

Revise A4 as:

=MATCH(TRUE,$A$2:$P$2<>"",0)

still confirmed with control+shift+enter, not just enter.

Also, can this be put into a UDF at all?

I suppose so. Hope someone into VBA will take this up. But is such step really needed?

 

[nnklem]

Revise A4 as:

=MATCH(TRUE,$A$2:$P$2<>"",0)

still confirmed with control+shift+enter, not just enter.



I suppose so. Hope someone into VBA will take this up. But is such step really needed?

Aladin,

This now works 100% as far as I can tell. I can not thank you enough for your assistance. Ultimately, I would like to have this in a UDF because I have an entire group of custom functions that I must use to process this report entirely and it would be nice to have it saved in a more user friendly repetitive method. However, I can certainly get by with what you have provided and I will not complain. Thanks again MrExcel MVP.

 

[Aladin Akyurek]

Aladin,

This now works 100% as far as I can tell. I can not thank you enough for your assistance. Ultimately, I would like to have this in a UDF because I have an entire group of custom functions that I must use to process this report entirely and it would be nice to have it saved in a more user friendly repetitive method. However, I can certainly get by with what you have provided and I will not complain. Thanks again MrExcel MVP.

You are very welcome. Thanks for providing feedback.

 

[Batch21]

Hi there, sorry to dredge up an old post but I have read this topic and it's exactly what I am looking to do with another example of data. However I also want to use a dynamic range, I'm using twinned OFFSET statements in an attempt to automatically extend the range but it seems to be conflicting with the FREQUENCY statement (or array mechanism). This is what I have so far...

=MAX(FREQUENCY(IF(OFFSET(E5,0,0,1,COUNTA(E4:Z4))="",OFFSET(E5,0,0,1,COUNTA(E4:Z4))),IF(OFFSET(E5,0,0,1,COUNTA(E4:Z4))<>"",OFFSET(E5,0,0,1,COUNTA(E4:Z4)))))

E4:Z4 has year-months and can extend outwards to the right (runs E4 to M4 so far, with up to Z4 accounting for possible expansion).

What I really need is for the MAX FREQUENCY to find the first and last row reference with data and use this as the dynamic range to count the MAX FREQUENCY of blanks within.

Is this possible using MAX, FREQUENCY and an array?

Or should I create a different type of dynamic range, using ADDRESS or something?

You help is massively appreciated...

 

[barry houdini]

You need some COLUMN functions in there, like this

=MAX(FREQUENCY(IF(OFFSET(E5,0,0,1,COUNTA(E4:Z4))="",COLUMN(OFFSET(E5,0,0,1,COUNTA(E4:Z4)))),IF(OFFSET(E5,0,0,1,COUNTA(E4:Z4))<>"",COLUMN(OFFSET(E5,0,0,1,COUNTA(E4:Z4))))))

confirmed with CTRL+SHIFT+ENTER

 

[Batch21]

Aww, lifesaver!!!!

Next......

OFFSET(E5,0,0,1,COUNTA(E4:Z4)

How would I go about replacing the 1 with the first cell that has a value and the COUNTA with the cell with the last value...

I'm guessing MAX(>0,E4:Z4,0) or something like that for the first cell with value....

In addition, if I do this within both of the OFFSET statements including the one enclosed in columns is it still going to work....?

 

[barry houdini]

Are you saying that E4:Z4 might only be populated from, for example, H4 and up to W4 and in that case you want the formula to apply to H5:W5? If so there might be easier ways.......would there be any blanks between the first and last values in row 4? If not then you should be able to use this version

=MAX(FREQUENCY(IF(E$4:Z$4<>"",IF(E5:Z5="",COLUMN(E5:Z5))),IF(E$4:Z$4<>"",IF(E5:Z5<>"",COLUMN(E5:Z5)))))