Count negative values before first positive

[loudnoiseman]

Hi Everyone,

I'm doing some amateur stock analysis and have a system set up to return price gains/losses 100 days forward in time based on various factors of pressure looking 100 days back.

My B column lists forward looking positive/negative values and columns C thru L are backward looking factor values and I need a formula in $M$1 that will count how many negatives appear in B column before the first positive, each time I sort factor columns.

I need to repeat a zillion times with macros so I think this solution will greatly speed up many weeks worth of work.

Thanks so much!!

 

[JamesCanale]

MrExcelPlayground6.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1	Column											7
2	-10
3	-9
4	-8
5	-7
6	-6
7	-5
8	-4
9	3
10	-1
11	-1
12	-1
13	-1
14	2
15	3
16	4
17	5
18	6
19	7
20	8
21	9
22	10
23	11
24	12
25	13
26	14
Sheet10

Cell Formulas
Range	Formula
M1	M1	=MIN(IF(IF(B2:B26<0,1,0)=0,ROW(B2:B26)-ROW(B2),""))

 

[loudnoiseman]

Perfect!!

Thank you James