We help IT Professionals succeed at work.

Splitting date range into weekly buckets

Gerald Connolly
Gerald Connolly used Ask the Experts™
on
i have the following data

      Daily Avg
04/09/2009      
22/10/2009      26.34029356
27/01/2010      94.54060731
23/04/2010      83.35397306
21/07/2010      19.88248141
16/08/2010      14.78855542
17/08/2010      0
25/10/2010      24.35493734
28/01/2011      101.1063237
20/04/2011      66.19501607

i.e. 4-Sep-2009 to 22-Nov-2009 the daily average was 26.34...

what i need is to split this into 52 weekly buckets per year, is this something i can do with Excel functions or does it need VBA programming
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013

Commented:
So what does the 94.54... represent? If you were to achieve what you want then how would the results look? Presumably each "bucket" during that period would be equal to 26.34?

regards, barry
Yup each week during the period would be equal to 26.34.

i tried creating a table of 52 entries with week beggining dates in the first column and this in the second =VLOOKUP(B58,data,2,TRUE) but it doesnt quite cut it.
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

in what way does ths not quite cut it? How about moving the values one row up? See attached.

cheers, teylyn
Book1.xlsx
Hey @teylyn, that works a treat (i knew it should be that easy, but couldnt get it to gel)

Only issue is that it doesnt stop when the data stops and it fills in the rest of 2011 with the 66.195 any ideas on that?
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

applied to the sample I posted earlier: In G2 and copied down

=IF(F2>MAX(B:B),"",VLOOKUP(F2,$B$2:$C$11,2,1))

cheers, teylyn

Commented:
Hi,

You can use the function WeekNum if you have the 'Analysis Toolpak' add-in ticked on, (menu > Tools > Add-ins).  This add-in is standard Microsoft but is not enabled by default on installation.

2 parameters, first is the date which is required, and the second, which is optional, determines if the week starts on a Sunday (value is 1) or a Monday (value is 2); default is Sunday.

For example:  If cell A1 has yesterdays date in it (07-Aug-2011, i.e. Sunday) you get the following:

=weeknum(A1) - result is: 33     <-- default of 1 is applied and week starts on Sunday.
=weeknum(A1,1) - result is: 33  
=weeknum(A1,2) - result is: 32  <--Week starts on Monday, i.e. Sunday is the last day of the previous week

Hope this helps.

Nero
Good solution, easy to follow, Excellent response.