Checking for consecutive numbers

[sonyaltec]

Hi,

Please help. Looking for a formula dynamic enough to use through out the entire year to detect consecutive non zero months. However, every consecutive 3 months block will form a cut off resetting the count to 0 again (Scenario A). The formula needs to follow the moving date. Expanding on that a second formula with the same logic but detecting every consecutive month with values greater than or equal to 3 (Scenario B). Illustrated below:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	13/3/2023
2
3		January	February	March	April	May	June	July	August	September	October	November	December	Consecutive Months Indicator
4	Scenario 1 (a)	1	2											2
5	Scenario 1 (b)	2	1	5	1									1
6	Scenario 1 (c)	1	0	6	1	7								0
7	Scenario 2 (a)	3	0	3										1
8	Scenario 2 (b)	3	6	3	7	3	9	3						1
9	Scenario 2 (c)	3	3	1	2	3	3	7	8	1				0
Sheet1

Cell Formulas
Range	Formula
A1	A1	=TODAY()-1

 

[James006]

Hi,
You can test following formula for cell B5

=AND(B5<>0,C5<>0,D5<>0,MOD(COLUMN()-1,3)=1)

 

[bebo021999]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3		January	February	March	April	May	June	July	August	September	October	November	December	Consecutive Months Indicator
4	Scenario 1 (a)	1	2											2
5	Scenario 1 (b)	2	1	5	1									1
6	Scenario 1 (c)	1	0	6	1	7								0
7	Scenario 2 (a)	3	0	3										1
8	Scenario 2 (b)	3	6	3	7	3	9	3						1
9	Scenario 2 (c)	3	3	1	2	3	3	7	8	1				0
Sheet1

Cell Formulas
Range	Formula
N4:N9	N4	=MOD(LOOKUP(2,1/(B4:M4>0)/(B4:M4<>""),COLUMN(B4:M4))-IFERROR(LOOKUP(2,1/(B4:M4=0)/(B4:M4<>""),COLUMN(B4:M4)),1),3)

 

[James006]

Hi again,
For the Conditional Formatting rule, starting in cell B5, you can test:

=AND(OFFSET($B5,0,INT(INT(COLUMN()-2)/3)*3)<>0,OFFSET($C5,0,INT(INT(COLUMN()-2)/3)*3)<>0,OFFSET($D5,0,0)<>INT(INT(COLUMN()-2)/3)*3)

and apply it right and down to all scenarios.

 

[sonyaltec]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3		January	February	March	April	May	June	July	August	September	October	November	December	Consecutive Months Indicator
4	Scenario 1 (a)	1	2											2
5	Scenario 1 (b)	2	1	5	1									1
6	Scenario 1 (c)	1	0	6	1	7								0
7	Scenario 2 (a)	3	0	3										1
8	Scenario 2 (b)	3	6	3	7	3	9	3						1
9	Scenario 2 (c)	3	3	1	2	3	3	7	8	1				0
Sheet1

Cell Formulas
Range	Formula
N4:N9	N4	=MOD(LOOKUP(2,1/(B4:M4>0)/(B4:M4<>""),COLUMN(B4:M4))-IFERROR(LOOKUP(2,1/(B4:M4=0)/(B4:M4<>""),COLUMN(B4:M4)),1),3)

hi bebo021999, theres an error where the calculation doesnt work if the latest month is 0.

Consecutive Month Formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	13/3/2023	9
2
3		1	2	3	4	5	6	7	8	9	10	11	12	Consecutive Months Indicator
4	Scenario 1 (a)	1	2	3	1	0	1	0	1	0				2
Sheet1

Cell Formulas
Range	Formula
A1	A1	=TODAY()-1
N4	N4	=MOD(LOOKUP(2,1/(B4:M4>0)/(B4:M4<>""),COLUMN(B4:M4))-IFERROR(LOOKUP(2,1/(B4:M4=0)/(B4:M4<>""),COLUMN(B4:M4)),1),3)

also i made an error in the assumption previously. it should disregard the current month (latest month) number when counting consecutive month. Meaning if we are in the month of May, (feb 0,march 2, april 2) consecutive month shown will be 2 regardless of what number is in May.

 

[James006]

Hi,
Did you test the formula in post # 4 ...

 

[sonyaltec]

Hi,
Did you test the formula in post # 4 ...

Sorry could you guide me through how to achieve that? Tried this but did not see any results.