Max Consecutive Blank Cells between 1st non-blank cell and last non-blank cell

[tkraft]

Hello Group, I am attempting to determine the max consecutive blank cells in a row of data between the 1st and last non-blank cells of a range. This is what I have come up with on my own but I seem to stuggling to get it to work. Would anyone have any insight? Thanks


=MAX(FREQUENCY(IF(LOOKUP(2,1/ISNUMBER(1/(1:1<>"")),COLUMN(1:1))-MATCH(TRUE,1:1<>"",0)+1="",COLUMN(LOOKUP(2,1/ISNUMBER(1/(1:1<>"")),COLUMN(1:1))-MATCH(TRUE,1:1<>"",0)+1)),IF(LOOKUP(2,1/ISNUMBER(1/(1:1<>"")),COLUMN(1:1))-MATCH(TRUE,1:1<>"",0)+1<>"",COLUMN(LOOKUP(2,1/ISNUMBER(1/(1:1<>"")),COLUMN(1:1))-MATCH(TRUE,1:1<>"",0)+1))))

 

[Nishant94]

Assuming You have data in Column B

=max(small(if((b1:index(b:b,lookup(2,1/(b:b<>""),row(b:b))))<>"",row((b1:index(b:b,lookup(2,1/(b:b<>""),row(b:b)))))),row(b1:index(b:b,countif((b1:index(b:b,lookup(2,1/(b:b<>""),row(b:b)))),"<>")-1))+1)-small(if((b1:index(b:b,lookup(2,1/(b:b<>""),row(b:b))))<>"",row((b1:index(b:b,lookup(2,1/(b:b<>""),row(b:b)))))),row(b1:index(b:b,countif((b1:index(b:b,lookup(2,1/(b:b<>""),row(b:b)))),"<>")-1)))-1)

 

[Nishant94]

For Row Wise Data:

=MAX(SMALL(IF((A1:INDEX(1:1,LOOKUP(2,1/(1:1<>""),COLUMN(1:1))))<>"",COLUMN((A1:INDEX(1:1,LOOKUP(2,1/(1:1<>""),COLUMN(1:1)))))),COLUMN((A1:INDEX(1:1,COUNTIF(1:1,"<>")-1)))+1)-SMALL(IF((A1:INDEX(1:1,LOOKUP(2,1/(1:1<>""),COLUMN(1:1))))<>"",COLUMN((A1:INDEX(1:1,LOOKUP(2,1/(1:1<>""),COLUMN(1:1)))))),COLUMN((A1:INDEX(1:1,COUNTIF(1:1,"<>")-1))))-1)

,Where data is in Row 1

For Column Wise Data:

=MAX(SMALL(IF((A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))))<>"",ROW((A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A)))))),ROW((A1:INDEX(A:A,COUNTIF(A:A,"<>")-1)))+1)-SMALL(IF((A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))))<>"",ROW((A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A)))))),ROW((A1:INDEX(A:A,COUNTIF(A:A,"<>")-1))))-1)

,Where data is in Column A

 

[tkraft]

Hi Nishant,
Thank you for the excellent response. I have attached a sample of my data.

Row Labels	November 02, 2015	November 03, 2015	November 04, 2015	November 05, 2015	November 06, 2015	November 08, 2015	November 11, 2015	November 12, 2015	November 13, 2015	November 14, 2015	November 15, 2015	November 16, 2015	November 17, 2015	November 18, 2015	November 19, 2015	November 20, 2015	November 21, 2015
3
4
10
13
14
21
41
51
55
57
41216	30	105	15
41230			7.5			2.5									1.5	11
41408				9	9
41774							3.5	14	14	7	10.5	10.5	3.5		3.5
41939										18.8	18.8	10.6
42121													10	10
42343																	5.1
42353																	42
42430
42700

<colgroup><col width="182" style="width: 137pt; mso-width-source: userset; mso-width-alt: 6656;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;" span="17"> <tbody> </tbody>

Unfortunatel I am unable to the formula to wor.

For Row Wise Data:

=MAX(SMALL(IF((A1:INDEX(1:1,LOOKUP(2,1/(1:1<>""),COLUMN(1:1))))<>"",COLUMN((A1:INDEX(1:1,LOOKUP(2,1/(1:1<>""),COLUMN(1:1)))))),COLUMN((A1:INDEX(1:1,COUNTIF(1:1,"<>")-1)))+1)-SMALL(IF((A1:INDEX(1:1,LOOKUP(2,1/(1:1<>""),COLUMN(1:1))))<>"",COLUMN((A1:INDEX(1:1,LOOKUP(2,1/(1:1<>""),COLUMN(1:1)))))),COLUMN((A1:INDEX(1:1,COUNTIF(1:1,"<>")-1))))-1)

,Where data is in Row 1

For Column Wise Data:

=MAX(SMALL(IF((A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))))<>"",ROW((A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A)))))),ROW((A1:INDEX(A:A,COUNTIF(A:A,"<>")-1)))+1)-SMALL(IF((A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))))<>"",ROW((A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A)))))),ROW((A1:INDEX(A:A,COUNTIF(A:A,"<>")-1))))-1)

,Where data is in Column A

 

[Nishant94]

Can you please elaborate on what exactly you want and where you want the results.

Please share sample results as well along with the sample data.

 

[Nishant94]

Is this what you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	November 02, 2015	November 03, 2015	November 04, 2015	November 05, 2015	November 06, 2015	November 08, 2015	November 11, 2015	November 12, 2015	November 13, 2015	November 14, 2015	Max gaps
2	14		25				63			52	3
3											0
4		26					25				4
5											0
6			65	15		63				36	3
7											0
8								15			0
9											0
10											0
11											0
12	30	105	15								0
13			7.5				2.5		1.5	11	3
14				9	9						0
15							3.5	14	3.5		0
Sheet5

Cell Formulas
Range	Formula
K2	{=IF(COUNTIF(A2:J2,"<>")<2,0,MAX(SMALL(IF((A2:INDEX(A2:J2,LOOKUP(2,1/(A2:J2<>""),COLUMN(A2:J2))))<>"",COLUMN((A2:INDEX(A2:J2,LOOKUP(2,1/(A2:J2<>""),COLUMN(A2:J2)))))),COLUMN((A2:INDEX(A2:J2,COUNTIF(A2:J2,"<>")-1)))+1)-SMALL(IF((A2:INDEX(A2:J2,LOOKUP(2,1/(A2:J2<>""),COLUMN(A2:J2))))<>"",COLUMN((A2:INDEX(A2:J2,LOOKUP(2,1/(A2:J2<>""),COLUMN(A2:J2)))))),COLUMN((A2:INDEX(A2:J2,COUNTIF(A2:J2,"<>")-1)))))-1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tkraft]

Thanks

Is this what you want:

	A	B	C	D	E	F	G	H	I	J	K
1	November 02, 2015	November 03, 2015	November 04, 2015	November 05, 2015	November 06, 2015	November 08, 2015	November 11, 2015	November 12, 2015	November 13, 2015	November 14, 2015	Max gaps
2	14		25				63			52	3
3											0
4		26					25				4
5											0
6			65	15		63				36	3
7											0
8								15			0
9											0
10											0
11											0
12	30	105	15								0
13			7.5				2.5		1.5	11	3
14				9	9						0
15							3.5	14	3.5		0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula K2 {=IF(COUNTIF(A2:J2,"<>")<2,0,MAX(SMALL(IF((A2:INDEX(A2:J2,LOOKUP(2,1/(A2:J2<>""),COLUMN(A2:J2))))<>"",COLUMN((A2:INDEX(A2:J2,LOOKUP(2,1/(A2:J2<>""),COLUMN(A2:J2)))))),COLUMN((A2:INDEX(A2:J2,COUNTIF(A2:J2,"<>")-1)))+1)-SMALL(IF((A2:INDEX(A2:J2,LOOKUP(2,1/(A2:J2<>""),COLUMN(A2:J2))))<>"",COLUMN((A2:INDEX(A2:J2,LOOKUP(2,1/(A2:J2<>""),COLUMN(A2:J2)))))),COLUMN((A2:INDEX(A2:J2,COUNTIF(A2:J2,"<>")-1)))))-1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>