Excel question

[Richards]

Hello,

I need help with a formula:
In cell A i have this:
0
0
1
0
0
0
1
0
1
In cell B i want to see how many steps there are between the 1's, in the example between the first 1 and the next are 3 zero's.

Anyone ?

Greetings
Richard

[JmacDaddy]

Try the "if" function.

For instance in cell B5 the formula could be "=if(a5=0,1+b4,0)"

Copy that formula all the way down coumn B

In your example the results would be

ColA      ColB
0             1
0             2
1             0
0             1
0             2
0             3
1             0
0             1
1             0

I hope that helps.  If not, let's try again.

[Richards]

Hello Jmac,

Thanks for the reply, i still get an error:  =if(a5=0,1+b4,0)

solution ?

[MattyMattz]

RIchard,

make sure your entering JMac's formula in cell B5, then drag it up and down... if you enter than in cell B2 (or anywhere else) it won't work.  You may also need to set up a different cell for your first cell.

Matt

[Richards]

I did still getting an error in the marked bold part ??

[MattyMattz]

not sure if it'll make a difference but try,

=if(a5=0,b4+1,0)

[Richards]

.... ......still there is an error......

[MattyMattz]

Hey Richard,

here we go:

Say you have the following in Column A

[table=,]
1
0
0
0
0
0
1
0
0
1
1
0
0
0
0
0
0
1
1
1
0
0
0
1
[/table]


This is what you need to do.  In Column B, in the first cell (Row 1) write the following:

=IF(A1="","",IF(A1=0,1,0))

Now in the second cell in Column B (Row 2) write the following:

=IF(A2="","",IF(AND(A1=1,A2=0),1,IF(AND(A1=1,A2=1),0,IF(A2=1,B1,B1+1))))

Now just drag down cell 2 as far as you want and you'll get the following:

[table=,]
1,0
0,1
0,2
0,3
0,4
0,5
1,5
0,1
0,2
1,2
1,0
0,1
0,2
0,3
0,4
0,5
0,6
1,6
1,0
1,0
0,1
0,2
0,3
1,3
[/table]

That should do it.  Not pretty but it works.

Matt

[MattyMattz]

-