Avatar of Gerald Connolly
Gerald Connolly
Flag 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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Gerald Connolly

8/22/2022 - Mon
barry houdini

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
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
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gerald Connolly

ASKER
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nero74

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

ASKER
Good solution, easy to follow, Excellent response.