fill with 1 or 0 with one condition

JoaoGabriel

New Member
Joined
Jun 6, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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
ID2020/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
1241413453422
231241475474
21431523941112
122141528
573011089
42842051216100
2838300104

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
Joined
Mar 31, 2015
Messages
2,394
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,976
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top