Consecutive occurrence based on current week

gd6noob

Board Regular
Joined
Oct 20, 2017
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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 weeks4 ConsecutiveWeek 23Week 24Week 25Week 26Week 27Week 28Week 29Week 30Week 31Week 32Week 33Week 34Week 35Week 36Week 37Week 38Week 39Week 40Week 41Week 42Week 43Week 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

Board Regular
Joined
Oct 20, 2017
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Oct 20, 2017
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,336
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.
 
Solution

gd6noob

Board Regular
Joined
Oct 20, 2017
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,119
Messages
5,546,045
Members
410,722
Latest member
Jojo3008
Top