Link to home
Create AccountLog in
Avatar of Gerald Connolly
Gerald ConnollyFlag for Australia

asked on

Splitting date range into weekly buckets

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
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of Gerald Connolly

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.