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

 

[Jon von der Heyden]

Hi RK and welcome to the board.

No doubt a slicker way, but:

Excel 2013
	A	B	C	D	E	F	G	H	I
1	N 1	N2	N 3	N 4	N 5	N 6	N 7		Result
2	0	0	0	0	1	2	3		4
3	0	0	1	2	3	0	1		2
4	0	0	0	1	2	0	0		3
5	0	0	0	0	0	0	0		7
Sheet1

Cell Formulas
Range	Formula
I2	{=MIN(IFERROR(MATCH({1;2;3;4;5;6;7;8;9},A2:H2,0),COLUMNS(A2:G2)+1))-1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AliGW]

It works fine without entering it as an array formula, so why bother? What am I missing? It also works without the row of N headings - what are they for?

 

[jorismoerings]

Hi,

can be done without array formula.
have a look at this:

Book1
	A	B	C	D	E	F	G	H	I
1	0	0	0	0	1	2	3		4
2	0	0	1	2	3	0	1		2
3	0	0	0	1	2	0	0		3
4	0	0	0	0	0	0	0		7
Sheet2

Cell Formulas
Range	Formula
I1	=IF(COUNT(A1:G1)=MATCH(1,A1:G1,1),COUNTIF(A1:G1,0),MATCH(1,A1:G1,1)-1)
I2	=IF(COUNT(A2:G2)=MATCH(1,A2:G2,1),COUNTIF(A2:G2,0),MATCH(1,A2:G2,1)-1)
I3	=IF(COUNT(A3:G3)=MATCH(1,A3:G3,1),COUNTIF(A3:G3,0),MATCH(1,A3:G3,1)-1)
I4	=IF(COUNT(A4:G4)=MATCH(1,A4:G4,1),COUNTIF(A4:G4,0),MATCH(1,A4:G4,1)-1)

 

[Jon von der Heyden]

Hi

Try entering in 1 in A4.

Hi,

can be done without array formula.
have a look at this:

	A	B	C	D	E	F	G	H	I

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I1[/TH]
[TD="align: left"]=IF(COUNT(A1:G1)=MATCH(1,A1:G1,1),COUNTIF(A1:G1,0),MATCH(1,A1:G1,1)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(COUNT(A2:G2)=MATCH(1,A2:G2,1),COUNTIF(A2:G2,0),MATCH(1,A2:G2,1)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=IF(COUNT(A3:G3)=MATCH(1,A3:G3,1),COUNTIF(A3:G3,0),MATCH(1,A3:G3,1)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"]=IF(COUNT(A4:G4)=MATCH(1,A4:G4,1),COUNTIF(A4:G4,0),MATCH(1,A4:G4,1)-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jon von der Heyden]

It works fine without entering it as an array formula, so why bother? What am I missing? It also works without the row of N headings - what are they for?

I used inline array constant {1;2;3;4;5;6;7;8;9}, which is why it works without CSE, but it is inherently still an array formula. But you're right, CSE isn't necessary, it was just me on auto-pilot.

The headers are there purely because I like headers.

 

[AliGW]

Thanks for the clarification. I asked only because I understand array formulae to be heavier on the Excel file than non-array formulae, although I may not have that quite right.

 

[Jon von der Heyden]

Thanks for the clarification. I asked only because I understand array formulae to be heavier on the Excel file than non-array formulae, although I may not have that quite right.

You're welcome. An array formula is a means to an end. If we derive the same result then it doesn't necessarily mean that the array formula will be heavier. It really boils down to a number of factors, such as function choice, logic and volume of evaluations to compute a result. Sometimes an array formula does exactly what a stepped approach would do, but without adding to the grid, without expanding used range and other impacts.

In the case of my formula, sure it is likely heavier. I MATCH the presence of 9 numbers when indeed only 1 or 2 (or none) of those numbers even exist in the evaluation range. Indeed that may be unnecessary. But unless the formula will span a considerable volume of rows, I doubt it will be 'heavy'.

These days I've moved beyond the academic and no longer aim to provide the most effective solution, I tend to just aim to give a suitable practical suggestion. Sometimes though what I come up with is simply the only logic I can muster up at the time.

 

[AliGW]

Logic is all I have to work with, having never learnt to use Excel from an academic perspective, that and a large dose of curiosity.

 

[Jon von der Heyden]

Well you aroused my curiosity, i.e. curious to know whether or not I can muster something up that works that doesn't do unnecessary evaluations. I'm happy with my latest addition, below, but will happily await one of the formula guru's to come up with something more elegant.

Excel 2013
	A	B	C	D	E	F	G	H	I
1	N 1	N2	N 3	N 4	N 5	N 6	N 7		Result
2	0	0	0	0	1	2	3		4
3	0	0	1	2	3	0	1		2
4	0	0	0	1	2	0	0		3
5	0	0	0	0	0	0	0		7
Sheet1

Cell Formulas
Range	Formula
I2	=IF(SUM(A2:G2),MATCH(SMALL(A2:G2,COUNTIF(A2:G2,0)+1),A2:G2,0)-1,COLUMNS(A2:G2))