Counting max number of blanks between cells in a dynamic range

HTX_Excel_Dude

New Member
Joined
Nov 11, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:

Value1Value2Value3Value4Value5

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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff v2.xlsm
LMNOPQRSTUVWXYZAAABACADAEAFAG
16191618519518161610518812197101716
2???? ??????????  ??????  ???????? 2
Main
Cell Formulas
RangeFormula
L2:AE2L2=IF(MOD(L1,4)=0,"","??")
AG2AG2=MAX(FREQUENCY(IF(L2:AE2="",COLUMN(L2:AE2)),IF(L2:AE2="",0,COLUMN(L2:AE2))))


Ignore the formula in L2:AE2
 

HTX_Excel_Dude

New Member
Joined
Nov 11, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Do you have the LET function yet?
 

HTX_Excel_Dude

New Member
Joined
Nov 11, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Not yet unfortunately.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Not a problem, just means the formula is a bit longer.
+Fluff v2.xlsm
LMNOPQRSTUVWXYZAAABACADAEAFAG
1619161851951816161051881219716816
2???? ??????????  ??????  ????   2
Main
Cell Formulas
RangeFormula
L2:AE2L2=IF(MOD(L1,4)=0,"","??")
AG2AG2=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

New Member
Joined
Nov 11, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
In that case I'm not sure how to do it.
Hopefully somebody who's better with formulae will step in.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,544
Office Version
  1. 365
Platform
  1. Windows
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. :eek:

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
LMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
167161851951816161051881219716816
2???? ??????????  ??????  ????   2217
3   ??????????  ??????  ??    3316
Count Blanks
Cell Formulas
RangeFormula
L2:AE2L2=IF(MOD(L1,4)=0,"","??")
L3:AE3L3=IF(OR(MOD(L1,4)=0,L1=6,L1=7),"","??")
AG2:AG3AG2=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:AH3AH2=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:AI3AI2=LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,940
Messages
5,639,095
Members
417,072
Latest member
JaimeDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top