Consecutive Cell Formula

[MIHeartandSoul]

I am looking for assistance please. I am trying to count the number of 0's before there is a 1. Please see below...

Store	A	B	C	D	E	F	G	H	I	J	K	Sum
1	0	0	1	0	1	0	0	0	1	1	0	1
2	0	1	0	1	0	0	0	1	0	1	0	1
3	0	0	1	1	0	0	1	1	1	0	0	2
4	0	1	1	0	0	0	0	0	0	0	0	8
5	1	1	1	1	1	1	1	1	0	0	1	0

<colgroup><col><col span="12"></colgroup><tbody>
</tbody>

So with my example above store #1 has 1 '0' (column K) before there is a '1'.
Store #4 Has 8 '0's before there is a '1'.

Can anyone suggest a formula on how to do this calculation?

 

[Rick Rothstein]

Your labeling is a little confusing... you seem to indicate your values are in Columns A:K, but that would mean the Store column would not exist? So I'll give you two formula for the two different possibilities and you pick the one that matches your actual setup.


Store in Column A, Data in Columns B:L
-------------------------------------------------------------------
=12-MAX((A2:L2=1)*COLUMN(A2:L2))


No Store column, data in Columns A:K
-------------------------------------------------------------------
=11-MAX((A2:K2=1)*COLUMN(A2:K2))


Note: Commit these formulas using CTRL+SHIFT+ENTER and not just Enter by itself

 

[MIHeartandSoul]

Thank you so much for the fast reply. I am doing CTRL+SHIFT+ENTER and then pasting in the formula and I keep receiving: #value!. Any suggestions?

Your labeling is a little confusing... you seem to indicate your values are in Columns A:K, but that would mean the Store column would not exist? So I'll give you two formula for the two different possibilities and you pick the one that matches your actual setup.


Store in Column A, Data in Columns B:L
-------------------------------------------------------------------
=12-MAX((A2:L2=1)*COLUMN(A2:L2))


No Store column, data in Columns A:K
-------------------------------------------------------------------
=11-MAX((A2:K2=1)*COLUMN(A2:K2))


Note: Commit these formulas using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Rick Rothstein]

Thank you so much for the fast reply. I am doing CTRL+SHIFT+ENTER and then pasting in the formula..

That is the wrong order... paste the formula into the Formula Bar, then press CTRL+SHIFT+ENTER.

 

[MIHeartandSoul]

Oh my gosh Fabulous.... Thank you so much!!!