# Consecutive occurrence based on current week

#### gd6noob

##### Board Regular
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

#### gd6noob

##### Board Regular
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### gd6noob

##### Board Regular
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

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

##### MrExcel MVP
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
BDBEBF
1# of streaks, with length of 4 or moreStreak?
2112Yes
351Yes
450No
500No
Sheet3
Cell Formulas
RangeFormula
BD2:BD5BD2=COUNTIF(OFFSET(C2,0,\$BH\$2-14,1,13),">0")
BE2:BE5BE2=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:BF5BF2=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

##### Board Regular
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
BDBEBF
1# of streaks, with length of 4 or moreStreak?
2112Yes
351Yes
450No
500No
Sheet3
Cell Formulas
RangeFormula
BD2:BD5BD2=COUNTIF(OFFSET(C2,0,\$BH\$2-14,1,13),">0")
BE2:BE5BE2=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:BF5BF2=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.

Replies
3
Views
53
Replies
0
Views
40
Replies
3
Views
92
Replies
7
Views
67
Replies
0
Views
106