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.

 

[gd6noob]

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

Based on my example is post 1:
Shift A: Yes, 2 occurrence
Shift B: Yes, 1 occurrence
Shift C: No

 

[gd6noob]

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

Thanks, not sure if my version support TextJoin but will try on Monday when I return to office.

 

[jasonb75]

Based on my example is post 1:
Shift A: Yes, 2 occurrence
Shift B: Yes, 1 occurrence
Shift C: No

What about the week numbers to show in column B?

The first stream for Shift A runs for Weeks 26 - 30. Given that the 13 week period starts at week 28, should the result show 26-30 or 28 - 30 (which is only 3 weeks)?

Also, what week numbers for Shift B using the same logic as above?

 

[Eric W]

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.

I'm confused? The BE2 formula showed not only how many streaks there were, but showed the starting weeks? If you only want it to say "Yes", then try:

Book1
	BD	BE	BF
1		# of streaks, with length of 4 or more	Streak?
2	11	2	Yes
3	5	1	Yes
4	5	0	No
5	0	0	No
Sheet3

Cell Formulas
Range	Formula
BD2:BD5	BD2	=COUNTIF(OFFSET(C2,0,$BH$2-14,1,13),">0")
BE2:BE5	BE2	=SUM(--(FREQUENCY(IF(OFFSET(B2,0,$BH$2-13,,13)<>"",COLUMN(A1:M1)),IF(OFFSET(B2,0,$BH$2-13,,13)="",COLUMN(A1:M1)))>3))
BF2:BF5	BF2	=IF(SUM(--(FREQUENCY(IF(OFFSET(B2,0,$BH$2-13,,13)<>"",COLUMN(A1:M1)),IF(OFFSET(B2,0,$BH$2-13,,13)="",COLUMN(A1:M1)))>3)),"Yes","No")
Press CTRL+SHIFT+ENTER to enter array formulas.

This is based on the same data in my last post. The BE2 formula says how many streaks there are in the given 13-week range, the BF2 formula merely says Yes or No if there is at least one streak or not. Or use the original BE2 formula which shows the starting weeks. Keep in mind that there will be at most 2 such streaks in a 13-week period, since each streak requires 4 weeks, plus a gap, equals 5 weeks. You can only fit 2 entire 5 week periods in a 13-week period.

 

[gd6noob]

I'm confused? The BE2 formula showed not only how many streaks there were, but showed the starting weeks? If you only want it to say "Yes", then try:

Book1
	BD	BE	BF
1		# of streaks, with length of 4 or more	Streak?
2	11	2	Yes
3	5	1	Yes
4	5	0	No
5	0	0	No
Sheet3

Cell Formulas
Range	Formula
BD2:BD5	BD2	=COUNTIF(OFFSET(C2,0,$BH$2-14,1,13),">0")
BE2:BE5	BE2	=SUM(--(FREQUENCY(IF(OFFSET(B2,0,$BH$2-13,,13)<>"",COLUMN(A1:M1)),IF(OFFSET(B2,0,$BH$2-13,,13)="",COLUMN(A1:M1)))>3))
BF2:BF5	BF2	=IF(SUM(--(FREQUENCY(IF(OFFSET(B2,0,$BH$2-13,,13)<>"",COLUMN(A1:M1)),IF(OFFSET(B2,0,$BH$2-13,,13)="",COLUMN(A1:M1)))>3)),"Yes","No")
Press CTRL+SHIFT+ENTER to enter array formulas.

This is based on the same data in my last post. The BE2 formula says how many streaks there are in the given 13-week range, the BF2 formula merely says Yes or No if there is at least one streak or not. Or use the original BE2 formula which shows the starting weeks. Keep in mind that there will be at most 2 such streaks in a 13-week period, since each streak requires 4 weeks, plus a gap, equals 5 weeks. You can only fit 2 entire 5 week periods in a 13-week period.

Thanks a lot fo this, helped me a ton.