Consecutive occurrence based on current week

[gd6noob]

Hi guys,

I need some help with 2 formulas.

Column A - I would like a formula that counts for the past 13 weeks based on current week.
Column B - I would like to know if there are 4 or more consecutive occurrences , if possible, list the weeks of occurrences. If there is multiple occurrences, is it possible to list them all in 1 cell? (example, week 26-30 and week 32 to 35)

The value is a drop down list for user to pick but value does not matter, as long as theres a value.

Past 13 weeks

4 Consecutive

Week 23

Week 24

Week 25

Week 26

Week 27

Week 28

Week 29

Week 30

Week 31

Week 32

Week 33

Week 34

Week 35

Week 36

Week 37

Week 38

Week 39

Week 40

Week 41

Week 42

Week 43

Week 44

Shift A

50​

50​

50​

50​

50​

50​

50​

50​

50​

50​

50​

Shift B

60​

60​

60​

60​

60​

60​

60​

60​

60​

Shift C

Shift D

Thank you in advanced.

 

[Eric W]

There are a few questions. When you say "past 13 weeks", do you mean Week 23-35, or 32-44? Are you going to add columns to the right (or left), and want the formula to adapt? What do you want to do if the streak starts in the 13-week range, say at week 12, and goes into 12-15?

Here's a trial version. This goes from week 23 to 35. I added an empty column after the shift column to make it easier. It shows the start week of the streaks (there can only be 2 according to your rules), but not the length.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Past 13 weeks	4 Consecutive			Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44
2	11	Week 26 Week 32	Shift A		50	50		50	50	50	50	50		50	50	50	50
3	0		Shift B															60	60	60	60	60	60	60	60	60
4	0		Shift C
5	0		Shift D
Sheet16

Cell Formulas
Range	Formula
A2:A5	A2	=COUNTIF(E2:Q2,">0")
B2:B5	B2	=IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(E1:N1)/(D2:M2="")/(SUBTOTAL(2,OFFSET(E2,0,COLUMN($E2:$N2)-COLUMN($E$2),,4))=4),1)),"")&" "&IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(E1:N1)/(D2:M2="")/(SUBTOTAL(2,OFFSET(E2,0,COLUMN($E2:$N2)-COLUMN($E$2),,4))=4),2)),"")

 

[gd6noob]

There are a few questions.
When you say "past 13 weeks", do you mean Week 23-35, or 32-44? if current week is 35, then calculate week 22 to week 34. If current week is 37, calculate week 24 to week 36

Are you going to add columns to the right (or left), and want the formula to adapt? I will put this at the end of week 52 instead of at the beginning.

What do you want to do if the streak starts in the 13-week range, say at week 12, and goes into 12-15? Not sure I understand this

Here's a trial version. This goes from week 23 to 35. I added an empty column after the shift column to make it easier. It shows the start week of the streaks (there can only be 2 according to your rules), but not the length.

 

[jasonb75]

@gd6noob please don't type your replies inside of a quoted post, although you had the foresight to make it easier to see your comments by changing the font colour, it can still be confusing and it prevents others from quoting parts of your reply if they need to.

Referring back to part of Eric's post that you said you didn't understand

What do you want to do if the streak starts in the 13-week range, say at week 12, and goes into 12-15?

Lets assume that this week is week 41. This would mean that the previous 13 weeks would cover Week 28 to Week 40.
Using your example from post 1, what results would you expect to see for Shift A and Shift B?

 

[Eric W]

I'm still hazy on some of your requirements, but here's how to move the formulas to the right.

Book1
	A	B	C																					X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM															BB	BC	BD	BE	BF	BG	BH
1			Week 1																					Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37															Week 52						Current Week
2	Shift A																								50	50		50	50	50	50	50		50	50	50	50																			11	Week 26, Week 32			36
3	Shift B																												60	60	60	60		60				60	60																	5	Week 27			Formula Range
4	Shift C																											80	80		80	80			80																					5				Week 23 - Week 35
5	Shift D																																																							0
Sheet3

Cell Formulas
Range	Formula
BD2:BD5	BD2	=COUNTIF(OFFSET(C2,0,$BH$2-14,1,13),">0")
BE2:BE5	BE2	=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN(OFFSET(C2,0,$BH$2-14,,10))/(OFFSET(B2,0,$BH$2-14,,10)="")/(SUBTOTAL(2,OFFSET(C2,0,$BH$2-14+{0,1,2,3,4,5,6,7,8,9},,4))=4),1)),"")&IFERROR(", "&INDEX($1:$1,AGGREGATE(15,6,COLUMN(OFFSET(C2,0,$BH$2-14,,10))/(OFFSET(B2,0,$BH$2-14,,10)="")/(SUBTOTAL(2,OFFSET(C2,0,$BH$2-14+{0,1,2,3,4,5,6,7,8,9},,4))=4),2)),"")
BH4	BH4	="Week "&BH2-13&" - Week "&BH2-1

I didn't know how you intend to specify which week it is, so I just put the current week in BH2. I couldn't rely on just looking for the last used entry in the row, since it could be empty for the given week. I also assume that there are no entries past the current week, so the issue of a range extending past the last week won't come up.

 

[gd6noob]

@gd6noob please don't type your replies inside of a quoted post, although you had the foresight to make it easier to see your comments by changing the font colour, it can still be confusing and it prevents others from quoting parts of your reply if they need to.

Referring back to part of Eric's post that you said you didn't understand


Lets assume that this week is week 41. This would mean that the previous 13 weeks would cover Week 28 to Week 40.
Using your example from post 1, what results would you expect to see for Shift A and Shift B?

Sorry, didnt mean to confuse people.

And correct, so based on what today's current week and not include the current week.

 

[gd6noob]

I'm still hazy on some of your requirements, but here's how to move the formulas to the right.

Book1
	A	B	C																					X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM															BB	BC	BD	BE	BF	BG	BH
1			Week 1																					Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37															Week 52						Current Week
2	Shift A																								50	50		50	50	50	50	50		50	50	50	50																			11	Week 26, Week 32			36
3	Shift B																												60	60	60	60		60				60	60																	5	Week 27			Formula Range
4	Shift C																											80	80		80	80			80																					5				Week 23 - Week 35
5	Shift D																																																							0
Sheet3

Cell Formulas
Range	Formula
BD2:BD5	BD2	=COUNTIF(OFFSET(C2,0,$BH$2-14,1,13),">0")
BE2:BE5	BE2	=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN(OFFSET(C2,0,$BH$2-14,,10))/(OFFSET(B2,0,$BH$2-14,,10)="")/(SUBTOTAL(2,OFFSET(C2,0,$BH$2-14+{0,1,2,3,4,5,6,7,8,9},,4))=4),1)),"")&IFERROR(", "&INDEX($1:$1,AGGREGATE(15,6,COLUMN(OFFSET(C2,0,$BH$2-14,,10))/(OFFSET(B2,0,$BH$2-14,,10)="")/(SUBTOTAL(2,OFFSET(C2,0,$BH$2-14+{0,1,2,3,4,5,6,7,8,9},,4))=4),2)),"")
BH4	BH4	="Week "&BH2-13&" - Week "&BH2-1

I didn't know how you intend to specify which week it is, so I just put the current week in BH2. I couldn't rely on just looking for the last used entry in the row, since it could be empty for the given week. I also assume that there are no entries past the current week, so the issue of a range extending past the last week won't come up.

Appreciate the help, the formula for the first question works perfectly.

For the second question, maybe too difficult, how about if there are 4 consecutive data then can we have it say "Yes".
Is it possible to say how many time there was consective data. for example, using your sheet, Shift A had 2 occurrence of consective times, week 26 to week 30 and week 32 to week 35.

 

[jasonb75]

And correct, so based on what today's current week and not include the current week.

Based on the current week being week 41, what results do you expect based on your example in post 1?

 

[CA_Punit]

I tried to combine all the weeks which are consecutive for only Shift A

It is just a sample will require modification in ranges, if it is what you want i try to modify

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Past 13 weeks	4 Consecutive			Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44
2	10	Week 28	Shift A			1	1	1		1	1	1	1	1		50	50	50	1		1	1	1	1	1
3	0		Shift B															60	60	60	60	60	60	60	60	60
4	0		Shift C
5	0		Shift D
6
7
8
9					Week 28//Week 32,Week 34//Week 37,Week 39//Week 43,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=COUNTIF(E2:Q2,">0")
B2:B5	B2	=IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(E1:N1)/(D2:M2="")/(SUBTOTAL(2,OFFSET(E2,0,COLUMN($E2:$N2)-COLUMN($E$2),,4))=4),1)),"")&" "&IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(E1:N1)/(D2:M2="")/(SUBTOTAL(2,OFFSET(E2,0,COLUMN($E2:$N2)-COLUMN($E$2),,4))=4),2)),"")
E9	E9	=TEXTJOIN(",",FALSE,IFERROR(INDEX($E$1:$Z$1,AGGREGATE(15,6,1/(1/(((IFERROR(INDEX(AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN($E:$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)),,1/(1/((COLUMN(A:Z)-1)))),-1)-AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN($E:$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)))<-1)*AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN($E:$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)))),COLUMN(A:Z))),"")&"//"&IFERROR(INDEX(E1:Z1,,IFERROR(INDEX(AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN($E:$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)),,AGGREGATE(15,6,1/(1/UNIQUE(IFERROR((((IFERROR(AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN($E:$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)),"")-INDEX(IFERROR(AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN($E:$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)),0),,COLUMN(A:Z)+1))=-1)*COLUMN(A:Z)=0)*COLUMN(A:Z),""),TRUE)),COLUMN(A:Z)))+3,"")),""))

 

[CA_Punit]

But your version of excel i will not be able to help as it doesn't have Textjoin function