# fill with 1 or 0 with one condition

#### JoaoGabriel

##### New Member
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?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### DRSteele

##### Well-known Member
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

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

Book1
ABCDEFGHIJKLMNOPQ
1CONSUMPTION PER MONTH
2ID2020/032020/042020/052020/062020/072020/082020/092020/102020/112020/122021/012021/02> 10 IN 3 SUBSEQUENT MONTHS> 10 IN 4 SUBSEQUENT MONTHS> 10 IN 6 SUBSEQUENT MONTHS
31.2E+0753422000
4231241475474000
52.1E+079411334412100
6122141528000
75730110891112131415111
842842051216100000
9283830011223344104110
Sheet7
Cell Formulas
RangeFormula
O3:O9O3=OR(COUNTIF(OFFSET(\$B3,0,COLUMN(\$B3:\$K3)-COLUMN(\$B3),1,3),">10")=3)+0
P3:P9P3=OR(COUNTIF(OFFSET(\$B3,0,COLUMN(\$B3:\$J3)-COLUMN(\$B3),1,4),">10")=4)+0
Q3:Q9Q3=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
ABCDEFGHIJKLMNOPQ
1CONSUMPTION PER MONTH
2ID2020/032020/042020/052020/062020/072020/082020/092020/102020/112020/122021/012021/02> 10 IN 3 SUBSEQUENT MONTHS> 10 IN 4 SUBSEQUENT MONTHS> 10 IN 6 SUBSEQUENT MONTHS
31241413453422111
4231241475474111
521431523941112111
6122141528000
7573011089111
842842051216100111
92838300104111
Sheet5
Cell Formulas
RangeFormula
O3:O9O3=OR(SUBTOTAL(9,OFFSET(\$B3,0,COLUMN(\$B3:\$K3)-COLUMN(\$B3),1,3))>10)+0
P3:P9P3=OR(SUBTOTAL(9,OFFSET(\$B3,0,COLUMN(\$B3:\$J3)-COLUMN(\$B3),1,4))>10)+0
Q3:Q9Q3=OR(SUBTOTAL(9,OFFSET(\$B3,0,COLUMN(\$B3:\$H3)-COLUMN(\$B3),1,6))>10)+0
Press CTRL+SHIFT+ENTER to enter array formulas.

Replies
1
Views
50
Replies
5
Views
79
Replies
7
Views
83
Replies
48
Views
303
Replies
6
Views
34

1,133,532
Messages
5,659,365
Members
418,499
Latest member
mbcmel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back