Rolling Week number - end of year

[PCRIDE]

I have a dashboard that displays data by week number. Since the turn of the year my rolling week table needs to reset at week 1.

For example I will trend 9 weeks of data today(weekNum()-9)
Then the next cell would be +1
Until I get 9 weeks minus the current week

Now I get week 52,53,54,55 etc and have to manually update it 50,51,52,1,2,3 etc

The problem will solve itself once we get to the 9th week this year, what can I use to reset the week?

My data is 9 rows worth the first cell today(weekNum)-9, then each cell after is +1

 

[sheetspread]

Something like =MAX(WEEKNUM(A1),9)?

 

[PCRIDE]

I don’t think that is working. It needs to be dynamic... so as the weeks approach Weeknumber 52, the next cell will be 1, not 53.

It’s a dashboard and needs to be fully automated based on dates. As a new week turns on Sunday, cell A9 would have week number 6, cell A1 would have week 50, then the next Sunday, A1 would be week 52, A9 would be week 7,


Does that make sense ? A rolling weekly table

What does the 9 do?

 

[PCRIDE]

I need it to reset after week 52 to go to Week 1.

The Dynamic date is based on Today WeekNum -9 weeks.

 

[sheetspread]

Can you post some more data so I can see the pattern you're looking for?

 

[PCRIDE]

This is how I've done it in the past. Built a look up table, with 2 years of dates, added the week number

https://flic.kr/p/24dnttB

Here is what the results are
https://flic.kr/p/EyboHB

I start at week 5, by doing VlookUp to get the current Week Number
Then minus 1 week for each cell. If the prior week was 52, then minus 1 week, and check for Week 1, then switch to week 52.

I need to to all this in one formula.

 

[sheetspread]

You said 9 weeks above, but the picture has 13 (???). Weeknum will start again at 1 each new year. Do you need weeknum at all? Why not something like:

Excel 2010
	E	F	G
1	1		1
2	2		1
3	3		1
4	4		1
5	5		1
6	6		1
7	7		1
8	8		2
9	9		2
10	10		2
11	11		2
12	12		2
13	13		2
14	14		2
15	15		3
16	16		3
17	17		3
18	18		3
19	19		3
20	20		3
21	21		3
22	22		4
23	23		4
24	24		4
25	25		4
Sheet4

Cell Formulas
Range	Formula
E1	=MOD(ROW(A1)-1,52)+1
G1	=MOD(-1+ROUNDUP(ROW(A1)/7,0),52)+1

That cycles 1-52 or 7 rows of 1,2,3......52 then starts at 1?

 

[sheetspread]

Or this?

Excel 2010
	I	J
1	2/8/2018	45
2		46
3		47
4		48
5		49
6		50
7		51
8		52
9		1
10		2
11		3
12		4
13		5
Sheet4

Cell Formulas
Range	Formula
J1	=WEEKNUM($I$1-91+(7*ROW(A1)-7))

 

[PCRIDE]

Or this?

Excel 2010
	I	J
1	2/8/2018	45
2		46
3		47
4		48
5		49
6		50
7		51
8		52
9		1
10		2
11		3
12		4
13		5
Sheet4

Cell Formulas
Range	Formula
J1	=WEEKNUM($I$1-91+(7*ROW(A1)-7))

I think this will work. Let me test it.

The whole idea is the sheet I have is a dashboard, data gets loaded into it, and when the user opens the file, depending on the day that it is, that correlates to a week number, which must change the table, I then connect SUMIFS to the week numbers and report out on the trending for each week.

It can be 13 weeks, or 9 or any number, its configurable really. I can link this to a control panel and the user can configure how they want to see the trending.

Thank you !!

 

[PCRIDE]

Can you tell me what the formula is doing? I've never used ROW before