fill with 1 or 0 with one condition

[JoaoGabriel]

fill with 1 or 0 with one condition:
- 1 when there are consumption values greater than 10 in any interval of three subsequent months, within the twelve months analyzed;
- 0 when it doesn't

apply the same for any subsequent four and six months

the data looks like this and the columns created on the side must be filled with 1 or 0:

	CONSUMPTION PER MONTH
ID	2020/03	2020/04	2020/05	2020/06	2020/07	2020/08	2020/09	2020/10	2020/11	2020/12	2021/01	2021/02		> 10 IN 3 SUBSEQUENT MONTHS	> 10 IN 4 SUBSEQUENT MONTHS	> 10 IN 6 SUBSEQUENT MONTHS
12414134				5	3	4	2					2
2312414					7	5	4				7	4
21431523			9	4	11						12
1221415	2						8
5730110	89
4284205	12			16	10			0
2838300								10	4

can anyone help?

 

[DRSteele]

Read through this thread to see if it helps. What will be needed is helper rows (or columns if you transpose the Table). I use this set of formulas in various ways and I have never been able to come up with something simpler.

@Peter_SSs helped immensely with that algorithm.

 

[Eric W]

This will show the 1 or 0 based on if the consecutive values are over 10:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		CONSUMPTION PER MONTH
2	ID	2020/03	2020/04	2020/05	2020/06	2020/07	2020/08	2020/09	2020/10	2020/11	2020/12	2021/01	2021/02		> 10 IN 3 SUBSEQUENT MONTHS	> 10 IN 4 SUBSEQUENT MONTHS	> 10 IN 6 SUBSEQUENT MONTHS
3	1.2E+07				5	3	4	2					2		0	0	0
4	2312414					7	5	4				7	4		0	0	0
5	2.1E+07			9	4	11	33	44				12			1	0	0
6	1221415	2						8							0	0	0
7	5730110	89	11	12	13	14	15								1	1	1
8	4284205	12			16	10			0						0	0	0
9	2838300				11	22	33	44	10	4					1	1	0
Sheet7

Cell Formulas
Range	Formula
O3:O9	O3	=OR(COUNTIF(OFFSET($B3,0,COLUMN($B3:$K3)-COLUMN($B3),1,3),">10")=3)+0
P3:P9	P3	=OR(COUNTIF(OFFSET($B3,0,COLUMN($B3:$J3)-COLUMN($B3),1,4),">10")=4)+0
Q3:Q9	Q3	=OR(COUNTIF(OFFSET($B3,0,COLUMN($B3:$H3)-COLUMN($B3),1,6),">10")=6)+0
Press CTRL+SHIFT+ENTER to enter array formulas.

This will show the 1 or 0 if the SUM of consecutive cells is over 10:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		CONSUMPTION PER MONTH
2	ID	2020/03	2020/04	2020/05	2020/06	2020/07	2020/08	2020/09	2020/10	2020/11	2020/12	2021/01	2021/02		> 10 IN 3 SUBSEQUENT MONTHS	> 10 IN 4 SUBSEQUENT MONTHS	> 10 IN 6 SUBSEQUENT MONTHS
3	12414134				5	3	4	2					2		1	1	1
4	2312414					7	5	4				7	4		1	1	1
5	21431523			9	4	11						12			1	1	1
6	1221415	2						8							0	0	0
7	5730110	89													1	1	1
8	4284205	12			16	10			0						1	1	1
9	2838300								10	4					1	1	1
Sheet5

Cell Formulas
Range	Formula
O3:O9	O3	=OR(SUBTOTAL(9,OFFSET($B3,0,COLUMN($B3:$K3)-COLUMN($B3),1,3))>10)+0
P3:P9	P3	=OR(SUBTOTAL(9,OFFSET($B3,0,COLUMN($B3:$J3)-COLUMN($B3),1,4))>10)+0
Q3:Q9	Q3	=OR(SUBTOTAL(9,OFFSET($B3,0,COLUMN($B3:$H3)-COLUMN($B3),1,6))>10)+0
Press CTRL+SHIFT+ENTER to enter array formulas.