Counting max number of blanks between cells in a dynamic range

[HTX_Excel_Dude]

Hello Excel Wizards!

I'm looking for an excel formula that will count the maximum number of blanks (formula blanks of "") between any 2 values in a row. The row will contain many different values, and are dynamic. I have thousands of rows where the values will be in different columns.

Example:

Value1

Value2

Value3

Value4

Value5

So essentially I am looking for a formula that would return the number '3', because there are 3 blanks between 'Value3' and 'Value4', compared against 1 blank between 'Value2' and 'Value3', and also against 1 blank between 'Value4' and 'Value5'.

The blanks are not truly empty cells, but instead contain "" derived from a formula within that cell.

Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	6	19	16	18	5	19	5	18	16	16	10	5	18	8	12	19	7	10	17	16
2	??	??		??	??	??	??	??			??	??	??			??	??	??	??			2
Main

Cell Formulas
Range	Formula
L2:AE2	L2	=IF(MOD(L1,4)=0,"","??")
AG2	AG2	=MAX(FREQUENCY(IF(L2:AE2="",COLUMN(L2:AE2)),IF(L2:AE2="",0,COLUMN(L2:AE2))))

Ignore the formula in L2:AE2

 

[HTX_Excel_Dude]

Quite brilliant! Thank you!

My only problem is that it's counting the blanks at the end of my array. My row doesn't necessarily end with a value. Any adjustment I can make to this formula?

 

[Fluff]

Do you have the LET function yet?

 

[HTX_Excel_Dude]

Not yet unfortunately.

 

[Fluff]

Not a problem, just means the formula is a bit longer.

+Fluff v2.xlsm
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	6	19	16	18	5	19	5	18	16	16	10	5	18	8	12	19	7	16	8	16
2	??	??		??	??	??	??	??			??	??	??			??	??					2
Main

Cell Formulas
Range	Formula
L2:AE2	L2	=IF(MOD(L1,4)=0,"","??")
AG2	AG2	=MAX(FREQUENCY(IF(L2:INDEX(L2:AE2,XMATCH(1,--(L2:AE2<>""),0,-1))="",COLUMN(L2:INDEX(L2:AE2,XMATCH(1,--(L2:AE2<>""),0,-1)))),IF(L2:INDEX(L2:AE2,XMATCH(1,--(L2:AE2<>""),0,-1))="",0,COLUMN(L2:INDEX(L2:AE2,XMATCH(1,--(L2:AE2<>""),0,-1))))))

 

[HTX_Excel_Dude]

Sorry to be a pain, but I don't have access to XMATCH either. Can I use a variation of MATCH instead?

 

[Fluff]

In that case I'm not sure how to do it.
Hopefully somebody who's better with formulae will step in.

 

[Peter_SSs]

but I don't have access to XMATCH either

I presume that means you don't have the FILTER() function either?
Assuming you don't, AG2 below is a direct adaptation of Fluff's formula using functions available in earlier Excel versions.
However, I would seriously consider using a helper cell like column AI (that column could be hidden) which then makes the column AH formula much more readable/manageable.

Note too though that Fluff's original formula and theses do count blanks at the start of the range (see row 3). If that is a problem then I would introduce another helper to locate the first non-blank column and adjust the the column AH formula to use that value as well. Post back if you need this.

20 11 12.xlsm
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	6	7	16	18	5	19	5	18	16	16	10	5	18	8	12	19	7	16	8	16
2	??	??		??	??	??	??	??			??	??	??			??	??					2	2	17
3				??	??	??	??	??			??	??	??			??						3	3	16
Count Blanks

Cell Formulas
Range	Formula
L2:AE2	L2	=IF(MOD(L1,4)=0,"","??")
L3:AE3	L3	=IF(OR(MOD(L1,4)=0,L1=6,L1=7),"","??")
AG2:AG3	AG2	=MAX(FREQUENCY(IF(L2:INDEX(L2:AE2,LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1))="",COLUMN(L2:INDEX(L2:AE2,LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1)))),IF(L2:INDEX(L2:AE2,LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1))="",0,COLUMN(L2:INDEX(L2:AE2,LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1))))))
AH2:AH3	AH2	=MAX(FREQUENCY(IF(L2:INDEX(L2:AE2,AI2)="",COLUMN(L2:INDEX(L2:AE2,AI2))),IF(L2:INDEX(L2:AE2, AI2)="",0,COLUMN(L2:INDEX(L2:AE2,AI2)))))
AI2:AI3	AI2	=LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1)