Counting Number of Consecutive Negative/Positive Numbers in a Row

[JAV3784]

Hi,

I am analyzing large sets of data and have a column of negative and positive numbers in B2:B784. I am attempting to count how frequently a negative number appears consecutively 1,2,3,4,etc. times in a row and conversely the number of times a positive number occurs 1,2,3,4,etc times in a row. Any ideas how I can accomplish this? Thanks so much.

 

[Peter_SSs]

Could we have a smallish set of dummy sample data and the expected results with XL2BB so that we can see (& copy) exactly the sort of data you are dealing with and how the results should be presented?
(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

 

[JAV3784]

Thanks Peter. Having some trouble with the add-in but I'll do my best to describe my scenario here:

- I am analyzing the weekly returns of stocks going back 10 years to see how frequently the stock was down just 1 week before having a positive return, down 2 consecutive weeks before a positive return, etc.. These numbers are all in column B.
- My goal is to count:
-the number of times that a stock was negative just 1 week before being positive the following week
-the number of times that a stock was negative 2 weeks in a row before being positive the following week
-the number of times that a stock was negative 3 weeks in a row before being positive the following week
-etc etc...

Let me know if that is helpful. Thanks again.

 

[Peter_SSs]

Having some trouble with the add-in

Can you explain the trouble?

 

[JAV3784]

 

[Peter_SSs]

The file needs to be in a trusted location. Have a look through this thread about that.

 

[Cubist]

Here's something you can start with using helper columns- one for positive and one for negative. There may be a solution without the helpers that will follow.

Book1
	B	C	D	E	F	G	H
1	data	negative helper	positive helper		# consecutive	negative count	positive count
2	29	0	1		1	5	7
3	-28	1	0		2	2	2
4	8	0	5		3	1	0
5	46	0	0		4	2	1
6	23	0	0		5	0	1
7	35	0	0
8	32	0	0
9	-17	1	0
10	8	0	1
11	-34	4	0
12	-48	0	0
13	-5	0	0
14	-29	0	0
15	39	0	1
16	-44	3	0
17	-12	0	0
18	-41	0	0
19	3	0	1
20	-2	2	0
21	-5	0	0
22	44	0	2
23	19	0	0
24	-5	1	0
25	29	0	4
26	32	0	0
27	42	0	0
28	28	0	0
29	-33	1	0
30	14	0	1
31	-5	1	0
32	43	0	1
33	-25	4	0
34	-25	0	0
35	-20	0	0
36	-25	0	0
37	14	0	2
38	49	0	0
39	-17	2	0
40	-31	0	0
41	48	0	1
Sheet8

Cell Formulas
Range	Formula
C2:C41	C2	=(1-OR(B2>0,N(B1)<0))*(SUM(--(B2:XLOOKUP(FALSE,B2:$B$41<0,B2:$B$41,$B$41)<0)))
D2:D41	D2	=(1-OR(B2<0,N(B1)>0))*(SUM(--(B2:XLOOKUP(FALSE,B2:$B$41>0,B2:$B$41,$B$41)>0)))
F2:F6	F2	=SEQUENCE(MAX(C2:D41))
G2:G6	G2	=COUNTIFS(C2:C41,F2#)
H2:H6	H2	=COUNTIFS(D2:D41,F2#)
Dynamic array formulas.