Find pattern (positive, negative, positive)

[phil133]

Hi. I have a column that has numbers. (The first number will always be positive). I would like to get a "flag" (1 in another column) whenever the data goes from positive to negative and then positive again (no need for back to back to back). If I get a 1 it should stop looking at this pattern and look to find the next negative and get a 2 then. When that happens start finding for the 1 again (the first pattern). Here is an example to understand.


Column A Column B
3.86%
3.79%
-2.88%
-2.33%
3.36% 1
2.07%
4.65%
-3.87% 2
3.88%
-3.32%
8.20% 1
-0.46% 2
1.49%
2.62%
So, at B5 I get 1 because we have a positive first (A1) then at A3 a negative and then at A5 a positive. Now that it got the 1 it will look for the first negative which it gets at A8.
After that, it tries to find the 1 again. A9 is positive, A10 is negative and A11 is positive so B11 will be 1. A12 is negative so B12 will be 2.
I don't have a specific preference for a formula or a macro.


Hope you understand what I'm trying to do!
Thanks for any help!

P.S. I have asked the same question in another forum Find pattern (positive, negative, positive)

 

[mikerickson]

You could put this in B2 and drag down

=IF(SIGN(A1*A2)>0, "", 1.5-SIGN(A2)/2)

 

[Peter_SSs]

Does this do it?

B1 holds a 2, manually entered.
Formula in B4 is copied down.

Excel Workbook
	A	B
1		2
2	3.86%
3	3.79%
4	-2.88%
5	-2.33%
6	3.36%	1
7	2.07%
8	4.65%
9	-3.87%	2
10	3.88%
11	-3.32%
12	8.20%	1
13	-0.46%	2
14	1.49%
15	2.62%
1 2

 

[phil133]

Thank you both for you answer! I picked Peter_SSs formula because that is exactly what I want! Mikerickson your answer is very close too!

Thank you both for your time and effort!

 

[Peter_SSs]

You are welcome. Here's a considerably shorter formula that still appears to do what is required.

Excel Workbook
	A	B
1		2
2	3.86%
3	3.79%
4	-2.88%
5	-2.33%
6	3.36%	1
7	2.07%
8	4.65%
9	-3.87%	2
10	3.88%
11	-3.32%
12	8.20%	1
13	-0.46%	2
14	1.49%
15	2.62%
1 2 (2)

 

[phil133]

This works also! Thanks again!