Find Sequence Pattern ( + - ) in Data

[ibmy]

Hi Mr Excel,

Before I give data sample, let me explain the sequence pattern in picture and the criteria.

Criteria of Sequence Pattern :

(1) Must at least starting with postive number, follow by negative number. ( + - )

(2) Keep assign "1" the cycle until 'invalid' happen ( + - + - + - + - + - ......... )

(3) Invalid,Stop Assign : Posive / Negative number appear two time consecutively. ( + - + - - ) / ( + - + - + - ++ )

(4) Invalid,Stop Assign : 0 appear. ( + - + 0 ) / ( + - + - + - 0 )

plus minus.xlsb
	F	G	H	I
1		b	Expected Result
2
3		-0.4
4		1
5		0
6		0.9
7		-0.1	1
8		-0.3
9		0.1
10		0.1
11		-0.1	1
12		0.1	1
13		0.8
14		0
15		0.1
16		-0.6	1
17		0.7	1
18		-0.9	1
19		1	1
20		-1.6	1
21		0.7	1
22		0.3
23		-0.3	1
24		0.1	1
25		-0.6	1
26		-0.3
27		0.1
28		-0.6	1
29		-0.5
30		0.2
31		-0.1	1
32		0
33		0
34		0
35		0.1
36		0.1
37		-0.3	1
38
Sheet6

Expected result in Column H.
Dont mind any method of getting the result ( formula, column helper, vba, etc... ) as long as work fine in 300k row data.

Thanks in advance.

 

[C Moore]

Put in column I to compare

=IF(MAX(AND(SIGN(G5)=-1,SIGN(G4)=1)+0,IF(J4=1,AND(SIGN(G5)<>SIGN(G4),SIGN(G5)<>AND(SIGN(G5)=-1,SIGN(G4)=1)+0))+0)=1,1,"")

I built helper columns to step through, but this is the final single column answer.

 

[ibmy]

@C Moore thanks for reply,

I have 2 questions,

1 ) In formula, there is J4 reference, but there is none value in that cell
> I tried change J4 to G4, the result not same

2 ) Which row of "Column I" should I insert the formula?
> I tried insert the formula in I5 after change J4 to G4, the result not same

ab2.xlsb
	G	H	I
1	b	Expected Result	Formula
2
3	-0.4
4	1
5	0
6	0.9
7	-0.1	1	1
8	-0.3
9	0.1
10	0.1
11	-0.1	1	1
12	0.1	1
13	0.8
14	0
15	0.1
16	-0.6	1	1
17	0.7	1
18	-0.9	1	1
19	1	1
20	-1.6	1	1
21	0.7	1
22	0.3
23	-0.3	1	1
24	0.1	1
25	-0.6	1	1
26	-0.3
27	0.1
28	-0.6	1	1
29	-0.5
30	0.2
31	-0.1	1	1
32	0
33	0
34	0
35	0.1
36	0.1
37	-0.3	1	1
Sheet2

Cell Formulas
Range	Formula
I5:I37	I5	=IF(MAX(AND(SIGN(G5)=-1,SIGN(G4)=1)+0,IF(G4=1,AND(SIGN(G5)<>SIGN(G4),SIGN(G5)<>AND(SIGN(G5)=-1,SIGN(G4)=1)+0))+0)=1,1,"")

 

[Peter_SSs]

See if this does what you want.

23 05 17.xlsm
	G	H
1	b	Result
2
3	-0.4
4	1
5	0
6	0.9
7	-0.1	1
8	-0.3
9	0.1
10	0.1
11	-0.1	1
12	0.1	1
13	0.8
14	0
15	0.1
16	-0.6	1
17	0.7	1
18	-0.9	1
19	1	1
20	-1.6	1
21	0.7	1
22	0.3
23	-0.3	1
24	0.1	1
25	-0.6	1
26	-0.3
27	0.1
28	-0.6	1
29	-0.5
30	0.2
31	-0.1	1
32	0
33	0
34	0
35	0.1
36	0.1
37	-0.3	1
Pattern

Cell Formulas
Range	Formula
H3:H37	H3	=IF(AND(H2="",G2>0,G3<0),1,IF(AND(H2=1,SIGN(G2)*SIGN(G3)=-1),1,""))

 

[ibmy]

Thanks @Peter_SSs , the formula working great.

If I want the sequence at least starting negative number follow by postive number, I just change G2<0, G3>0 , right?

gh2.xlsb
	G	H	I
1	b	Starting - +	Starting + -
2
3	-0.4
4	1	1
5	0
6	0.9
7	-0.1		1
8	-0.3
9	0.1	1
10	0.1
11	-0.1		1
12	0.1	1	1
13	0.8
14	0
15	0.1
16	-0.6		1
17	0.7	1	1
18	-0.9	1	1
19	1	1	1
20	-1.6	1	1
21	0.7	1	1
22	0.3
23	-0.3		1
24	0.1	1	1
25	-0.6	1	1
26	-0.3
27	0.1	1
28	-0.6	1	1
29	-0.5
30	0.2	1
31	-0.1	1	1
32	0
33	0
34	0
35	0.1
36	0.1
37	-0.3		1
Sheet6

Cell Formulas
Range	Formula
H3:H37	H3	=IF(AND(H2="",G2<0,G3>0),1,IF(AND(H2=1,SIGN(G2)*SIGN(G3)=-1),1,""))
I3:I37	I3	=IF(AND(I2="",G2>0,G3<0),1,IF(AND(I2=1,SIGN(G2)*SIGN(G3)=-1),1,""))

 

[Peter_SSs]

Thanks @Peter_SSs , the formula working great.

You're welcome. Good news!

If I want the sequence at least starting negative number follow by postive number, I just change G2<0, G3>0 , right?

Looks good to me.