Help with a formula

[ricky]

Hello i am new her and hope you can help me with my problem:

In column A i have the following value's 0, 1 and -1.
Now i want in column B: we leave the 0 out, if A=-1 than column
should give 1, if the next one is again -1 than B should be 2 onl
after A is 1 than B should be 0 and start over again.
See example below, i want to see how long a -1 streak is before an 1 i
coming.


A B

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


Thanks, greetings

Richar

 

[Frank Kabel]

Hi Richard
one way: Enter the following in B1 as array formula (CTRL+SHIFT+ENTER):
=IF(A1=0,"",IF(A1=1,0,COUNTIF(INDIRECT("A" &
MAX(($A$1:A1=1)*ROW($A$1:A1))+1 & ":A" & ROW(A1)),-1)))
copy this down in column B

HTH
Frank

 

[ricky]

Frank: getting a error using this formula:

Good you give an example in this sheet.

Thanks Richar

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41972

 

[Frank Kabel]

Hi Richard

tried the formula your example and it works well. What error did you
get?

If you like, send me your email adress and I'll send you your example
sheet with the working formula included

Frank

 

[ricky]

Frank: my e-mail adres is (e-mail address removed)

Looking forward to see the sheet.

Richar