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]

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

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

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-ISNUMBER(1/A2:U2),COLUMN(A2:U2)),
    IF(ISNUMBER(1/A2:U2),COLUMN(A2:U2)))>=7,
    FREQUENCY(IF(1-ISNUMBER(1/A2:U2),COLUMN(A2:U2)),
    IF(ISNUMBER(1/A2:U2),COLUMN(A2:U2)))))

 

[nazarene]

Perfect formula!!
Thanks again Mr. Aladin, God bless us

 

[Aladin Akyurek]

Perfect formula!!
Thanks again Mr. Aladin, God bless us

You are welcome.

 

[nazarene]

Mr. Aladin, through your last formula that you have given to me, frankly until now I cant understand it, can you break each function and explain? thank in advance. you sorry.. coz I really want to learn excel

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-ISNUMBER(1/A2:U2),COLUMN(A2:U2)),
    IF(ISNUMBER(1/A2:U2),COLUMN(A2:U2)))>=7,
    FREQUENCY(IF(1-ISNUMBER(1/A2:U2),COLUMN(A2:U2)),
    IF(ISNUMBER(1/A2:U2),COLUMN(A2:U2)))))

Mr. Aladin, through your last formula that you have given to me, frankly until now I cant understand it, can you break each function and explain? thank in advance. you sorry.. coz I really want to learn excel

If you implemented the formula, you can use the F9 technique in order to see to what a given part of a formula evaluates.

For example, if you select the following bit:

IF(1-ISNUMBER(1/A2:U2),COLUMN(A2:U2))

of the formula on the formula bar, hit F9 to see what this bit delivers. In order not to loose the formula, hit escape, then choose again a part of the formula and hit F9. This way you should be able to figure out how the formula is constructed and works.

 

[nazarene]

Thank you Again Mr.Aladin, I will try this trick you've mentioned

 

[nomad_veritas]

Control+shift+enter, not just enter:

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

Hi Aladin,

I registered on the forum just so I could ask you a similar question! Instead of trying to find the max number of consecutive blank cells, I'm trying to find the average. Using the same example as above:

WORK WORK WORK WORK __ __ __ __ __ OFF OFF OFF OFF __ __ __ LVE LVE __ WORK WORK WORK.

For this row, it would count the total number of blank cells in the row and then average it across the total number of consecutive blank groups (ex: (5+3+1)/3=3). I thought that counting the total number of "___" in the row and dividing it by the formula you provided (and taking out the max part) would do it, but it didn't unfortunately. Do you have any suggestions for how I can achieve this?

Thanks in advance!

 

[Aladin Akyurek]

Hi Aladin,

I registered on the forum just so I could ask you a similar question! Instead of trying to find the max number of consecutive blank cells, I'm trying to find the average. Using the same example as above:

WORK WORK WORK WORK __ __ __ __ __ OFF OFF OFF OFF __ __ __ LVE LVE __ WORK WORK WORK.

For this row, it would count the total number of blank cells in the row and then average it across the total number of consecutive blank groups (ex: (5+3+1)/3=3). I thought that counting the total number of "___" in the row and dividing it by the formula you provided (and taking out the max part) would do it, but it didn't unfortunately. Do you have any suggestions for how I can achieve this?

Thanks in advance!

Control+shift+enter, not just enter:

=SUM(FREQUENCY(IF(A2:V2="",COLUMN(A2:V2)),IF(A2:V2<>"",COLUMN(A2:V2))))/
    INDEX(FREQUENCY(FREQUENCY(IF(A2:V2="",COLUMN(A2:V2)),IF(A2:V2<>"",COLUMN(A2:V2))),0),2)

Addendum.

Control+shift+enter:

=COUNTIFS(A2:V2,"=")/INDEX(FREQUENCY(FREQUENCY(IF(A2:V2="",COLUMN(A2:V2)),
    IF(A2:V2<>"",COLUMN(A2:V2))),0),2)

should suffice.

 

[nazarene]

Mr, Aladin How are you,
Can you help me again, I have this problem, I have a thousand of files on my excel
MIAP:352BSZG0800C10 Flange SO-Cu/Ni 2" EEMUA 145 Integral Type 16 Bar FF Cu-Ni 90-10
I want to separate the size of a pipe 2" from this data. I want to separate the 2" as per above word, can you please help me sir?
Thanks

 

[nazarene]

Mr. Aladin How are you,
Can you help me again, I have this problem, I have a thousand of files on my excel
MIAP:352BSZG0800C10 Flange SO-Cu/Ni 2" EEMUA 145 Integral Type 16 Bar FF Cu-Ni 90-10
I want to separate the size of a pipe 2" from this data. I want to separate the 2" as per above word, can you please help me sir?
Thanks