Help with a formula

  • Thread starter Thread starter ricky
  • Start date Start date
R

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
 
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
 
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
 
Frank: my e-mail adres is (e-mail address removed)

Looking forward to see the sheet.

Richar
 
Back
Top