How to count consecutive numbers based on the first number of the column

[Mathman]

Hi


Looking for a formula to count the consecutive numbers in 1 column based on the first Number/Column.


Then if the first number in the column is Zero the result is shown as a negative number but if the first number in the column is 1 the result is shown as a positive number.


If you can help it would be greatly appreciated.


Here is an example.

Result -1 -1 3 2 A B C D 1 0 0 1 1 2 1 1 1 1 3 0 1 1 0 4 0 0 0 0 5 0 0 0 0 6 1 0 1 1 7 1 1 1 1 8 1 1 0 1 9 1 0 1 1 10 1 1 1 1 11 1 1 1 0 12 1 1 0 0 13 0 0 0 0 14 1 0 1 0 15 1 1 0 1 16 1 0 1 1 17 1 1 1 1 18 1 1 1 1 19 1 1 1 1 20 1 1 1 1 THX <tbody> </tbody>

<tbody>
</tbody>

 

[AliGW]

I thought I had responded to this same query elsewhere, but I can't find it. Please explain your requirements in more detail.

 

[Mathman]

You did, hope this helps.

Let's look at column A as each Column will have it's own result. The starting point is to look at row 1 first. This will determine if the result will be expressed as a positive number or a negative number. Zero is negative and 1 is positive. In column A the first number is negative making the result expressed as a negative number, we then look at row 2. Row 2 is a 1. This means that based on the first row the Zero was followed by a 1 and the formula end as simply one consecutive negative number giving the result of -1

If you look at column C the first row number is 1 making the result a positive number of 1. you then look at row 2 and you still have a 1 bringing the total to 2. Then row 3 has another 1 with a new total of 3 but then when you look at row 4 you have a zero so the total consecutive numbers for this column would be 3 as the zero in row 4 stops the consecutive number count.




I use this to track consecutive days up or down for stocks. You can think of the rows(1 threw 20) as days. 1 would be today, 2 yesterday and so on. What I want to know is based on today how many consecutive days were up (positive number) or down (negative numbers)


I hope this makes sense. Thank you for the reply.

 

[AliGW]

Where did I respond? Was it a different forum? If so you need to provide a link to that thread.

Why is the result in B not -2?

 

[Mathman]

you simple asked me to clarify but you didn't suggest a formulae.

 

[AliGW]

Yes, but where? Please provide a link.

 

[Mathman]

For column B the result is -1 because the formulae is based on the first row, B1 is a zero making the result a negative number, then in row to you have a 1 being a positive number. Since you have only one zero followed by a 1 the calculation must end and all other rows are not needed giving you a result of only 1 consecutive Zero (result -1)

How to count consecutive numbers based on the first number of the column

 

[AliGW]

OK - so if the first value is a zero, we record -1, so there can be no other negative number result - is this correct?

 

[AliGW]

Try this in A1 copied across:

=IF(A4=0,-1,MATCH(0,A$4:A$23,0)-1)

Excel 2016 (Windows) 32 bit
	A	B	C	D
1	-1	-1	3	2
2	A	B	C	D
3
4	0	0	1	1
5	1	1	1	1
6	0	1	1	0
7	0	0	0	0
8	0	0	0	0
9	1	0	1	1
10	1	1	1	1
11	1	1	0	1
12	1	0	1	1
13	1	1	1	1
14	1	1	1	0
15	1	1	0	0
16	0	0	0	0
17	1	0	1	0
18	1	1	0	1
19	1	0	1	1
20	1	1	1	1
21	1	1	1	1
22	1	1	1	1
23	1	1	1	1
Sheet1

Cell Formulas
Range	Formula
A1	=IF(A4=0,-1,MATCH(0,A$4:A$23,0)-1)
B1	=IF(B4=0,-1,MATCH(0,B$4:B$23,0)-1)
C1	=IF(C4=0,-1,MATCH(0,C$4:C$23,0)-1)
D1	=IF(D4=0,-1,MATCH(0,D$4:D$23,0)-1)

 

[Mathman]

thx Ali but it doesn't quite fit. Take a look at this and tell me what you think. I listed several rows with your formulae result and the correct results.

	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	TRUE	TRUE	TRUE	#N/A
Your Formulae	-1	-1	-1	6	6	2	-1	-1	-1	-1	-1	6	6	6	-1	-1	-1	-1	-1	-1	#N/A
Corect Result	-1	-2	-1	3	3	1	-2	-1	-1	-1	-1	3	3	3	-1	-1	-2	-1	-1	-1	20
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	0	0	0	1	1	1	0	0	0	0	0	1	1	1	0	0	0	0	0	0	1
2	1	0	1	1	1	0	0	1	1	1	1	1	1	1	1	1	0	1	1	1	1
3	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
4	1	1	1	0	0	0	0	0	0	1	1	0	0	0	0	0	0	1	1	1	1
5	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	1	1
6	0	0	0	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	1	1
7	1	0	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1
8	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
9	1	1	0	1	1	1	1	0	0	0	0	1	1	1	1	1	1	1	1	1	1
10	1	0	1	1	1	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1
11	1	1	1	1	1	0	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1
12	1	1	1	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1
13	1	1	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1
14	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1
15	1	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
16	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
17	1	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
18	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
19	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
20	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1

<colgroup><col width="104" style="width: 78pt;"><col width="47" span="21" style="width: 35pt;"></colgroup><tbody>
</tbody>