Excel Formula for How to calculate first consecutive zeroes in excel?

[rohankekatpure1987]

My excel workbook consists of data below :

0 0 0 0 1 2 3
0 0 1 2 3 0 1
0 0 0 1 2 0 0
0 0 0 0 0 0 0

I want to calculate number of starting zeroes in each row. So my result for the above row would be:

0 0 0 0 1 2 3 => 4
0 0 1 2 3 0 1 => 2
0 0 0 1 2 0 0 => 3
0 0 0 0 0 0 0 => 7

I'm not concerned about the middle zeroes. What excel formula to use to find the starting zeroes in each row?

Thanks,
RK

 

[rohankekatpure1987]

Thanks All for the warm welcome. I'm also an excel enthusiast. Well the solutions above are a very good starting point for me. Sorry but they are failing in the below scenarios :

5 0 0 0 4 2 3
0 0 0 55 6 2 5

How can we modify logic to support 2 digit numbers as well?

 

[AliGW]

Well, your opening post said that you were looking for the zeroes BEFORE the first non-zero number, so you are now shifting the goalposts. Before anyone spends any more time on this, are there any other scenarios that they need to be aware of?

 

[rohankekatpure1987]

Apologies for the confusion. I'm new to the forum so missed this scenario. I guess there are no other scenarios in my question.

 

[AliGW]

How many non-blank numbers might there be before the first zero you want to count? And in that scenario, how does one identify the range of zeroes to count? Are you in fact looking for ranges of more than one consecutive zero?

 

[rohankekatpure1987]

My 1st non-blank number can be at any position : 1st ,2nd,3rd,5th position. I'm looking for the 0 consecutive ranges just before the start of non-zero number.

 

[Jon von der Heyden]

As a quick amendment to my first suggestion:

=MIN(IFERROR(MATCH({1;2;3;4;5;6;7;8;9},--LEFT(A2:H2,1),0),COLUMNS(A2:G2)+1))-1

Note - This IS an array formula and must be confirmed with Control+Shift+Enter

 

[Jon von der Heyden]

Second thoughts, I think this is neater:

=IF(SUM(A2:G2),MIN(IF(A2:G2>0,COLUMN(A2:G2)-1)),COLUMNS(A2:G2))

...again, array formula, so confirm with control+shift+enter

 

[rohankekatpure1987]

Thanks for your solution Jon von der Heyden. Really appreciate.

One question : Arrays Vs Offset/ Match combination better for solving such problems. I'm already having 60MB file; so would arrays further slow down the file calculations?

Second thoughts, I think this is neater:

=IF(SUM(A2:G2),MIN(IF(A2:G2>0,COLUMN(A2:G2)-1)),COLUMNS(A2:G2))

...again, array formula, so confirm with control+shift+enter

 

[Jon von der Heyden]

Hi again. Sorry for late reply.

Have you seen post #8?

It's never as simple as array vs. non-array. It really does depend on what the array formula is doing. If your file is slow, and exceeding 60MB, then I'm sure there are many other culprits at play.

 

[Aladin Akyurek]

Either just enter...

=IF(COUNTIFS(A2:G2,0)=COLUMNS(A2:G2),COLUMNS(A2:G2),MATCH(1,INDEX(1-(A2:G2=0),0),0)-1)

Or control+shift+enter (cse), not just enter:

=IF(COUNTIFS(A2:G2,0)=COLUMNS(A2:G2),COLUMNS(A2:G2),
  INDEX(FREQUENCY(IF(ISNUMBER($A$2:$G$2),IF($A$2:$G$2=0,
  COLUMN(A2:G2))),IF(1-(A2:G2=0),COLUMN($A$2:$G$2))),1))

The first formula is also an array-processing formula while it avoids cse.