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!

 

[nazarene]

Can Somebody help me, regarding formula that cannot solve for a week.
It goes like this.
I have a data. whole month of September. I want to know how many consecutive blanks that more or equal to seven. I only want to add all the consecutive blank cells that are more or equal to seven. less seven will not be counted.
Example. Sept. 1 to 7 is blanks and then Sept. 10 to 18 is also blanks and Sept. 21 to 30 is also blanks, so all in all the formula will count 25 blanks in the whole September.
can you help me, I've tried the frequency formula but cannot.
Please help me....

 

[Aladin Akyurek]

@nazarene

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A30="",ROW(A2:A30)),IF(1-(A2:A30=""),
   ROW(A2:A30)))>=7,FREQUENCY(IF(A2:A30="",ROW(A2:A30)),IF(1-(A2:A30=""),
   ROW(A2:A30)))))

If you have formulas in the target range that generates blanks which you wouldn't want to include in the count...

=SUM(IF(FREQUENCY(IF(ISBLANK(A2:A30),ROW(A2:A30)),
   IF(1-ISBLANK(A2:A30),ROW(A2:A30)))>=7,
   FREQUENCY(IF(ISBLANK(A2:A30),ROW(A2:A30)),
   IF(1-ISBLANK(A2:A30),ROW(A2:A30)))))

 

[nazarene]

Thank you very much Mr. Aladin you are truly an excel genius

 

[nazarene]

How about if the blanks that less than seven is not counted?
Only I want is to count all the the blanks that more or equal than seven, and less than seven it will not be counted.
Sorry mr. Aladin if too many Questions

 

[nazarene]

Its okey , just dis regarded my last query, I just change the row to column, as my date is column wise, thanks mr. aladin, god bless you

 

[Aladin Akyurek]

Thank you very much Mr. Aladin you are truly an excel genius

How about if the blanks that less than seven is not counted?
Only I want is to count all the the blanks that more or equal than seven, and less than seven it will not be counted.
Sorry mr. Aladin if too many Questions

Its okey , just dis regarded my last query, I just change the row to column, as my date is column wise, thanks mr. aladin, god bless you

You are welcome. Glad to see that you sorted it out.

 

[nazarene]

Here I am again, Mr. Aladin, sorry again, forgive me to ask you again.
Due to each cells have also a formula, if the cell are equals to zeros the cell has a "-" value. Instead of counting the blank cell, I need to count all cells that have a consecutive zeros that which are more or equal to 7, which represented by "-". Thank you again Mr. Aladin. Sorry for making you busy.
As shown below table
Sept1- Sept2- Sept3 -Sept4- Sept 5- Sept 6- Sept7- Sept8- Sept9 - Sept10- Sept11
- - - - - - - 10 - - 10 = 7

 

[Aladin Akyurek]

Here I am again, Mr. Aladin, sorry again, forgive me to ask you again.
Due to each cells have also a formula, if the cell are equals to zeros the cell has a "-" value. Instead of counting the blank cell, I need to count all cells that have a consecutive zeros that are more or equal to 7, which represented by "-". Thank you again Mr. Aladin. Sorry for making you busy.
As shown below table
1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 7-Sep 8-Sep 9-Sep 10-Sep
- - - - - - - 1.00 - 10.00 = 7

Are these values true 0's formatted to display a minus sign, or are they real minusses?

 

[nazarene]

No usually when the cells are equals to zero it came dash or - , its not actually a minus sign.
when the cells is equals to thousand like this 1,000, when the cells are equals to zero is like this "-" das

 

[nazarene]

If the formula in the cells were computed zero or "-", if that zero or "-" are more than seven consecutive the formula will count it.