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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,350
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Past 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
211Week 26 Week 32Shift A5050505050505050505050
30 Shift B606060606060606060
40 Shift C
50 Shift D
Sheet16
Cell Formulas
RangeFormula
A2:A5A2=COUNTIF(E2:Q2,">0")
B2:B5B2=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

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
@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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,350

ADVERTISEMENT

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

Book1
ABCXYZAAABACADAEAFAGAHAIAJAKALAMBBBCBDBEBFBGBH
1Week 1Week 22Week 23Week 24Week 25Week 26Week 27Week 28Week 29Week 30Week 31Week 32Week 33Week 34Week 35Week 36Week 37Week 52Current Week
2Shift A505050505050505050505011Week 26, Week 3236
3Shift B606060606060605Week 27Formula Range
4Shift C80808080805 Week 23 - Week 35
5Shift D0 
Sheet3
Cell Formulas
RangeFormula
BD2:BD5BD2=COUNTIF(OFFSET(C2,0,$BH$2-14,1,13),">0")
BE2:BE5BE2=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)),"")
BH4BH4="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

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

Board Regular
Joined
Oct 20, 2017
Messages
82
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Book1
ABCXYZAAABACADAEAFAGAHAIAJAKALAMBBBCBDBEBFBGBH
1Week 1Week 22Week 23Week 24Week 25Week 26Week 27Week 28Week 29Week 30Week 31Week 32Week 33Week 34Week 35Week 36Week 37Week 52Current Week
2Shift A505050505050505050505011Week 26, Week 3236
3Shift B606060606060605Week 27Formula Range
4Shift C80808080805 Week 23 - Week 35
5Shift D0 
Sheet3
Cell Formulas
RangeFormula
BD2:BD5BD2=COUNTIF(OFFSET(C2,0,$BH$2-14,1,13),">0")
BE2:BE5BE2=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)),"")
BH4BH4="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

Well-known Member
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Past 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
210Week 28Shift A11111111505050111111
30 Shift B606060606060606060
40 Shift C
50 Shift D
6
7
8
9Week 28//Week 32,Week 34//Week 37,Week 39//Week 43,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//
Sheet1
Cell Formulas
RangeFormula
A2:A5A2=COUNTIF(E2:Q2,">0")
B2:B5B2=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)),"")
E9E9=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

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
But your version of excel i will not be able to help as it doesn't have Textjoin function
 

Watch MrExcel Video

Forum statistics

Threads
1,114,608
Messages
5,548,968
Members
410,886
Latest member
sjohn627
Top