Separating Dates into Weeks

[marielouise1]

Hello,

I have attached a dataset: I have a list of data from different dates and I need the average for each week to be displayed.
I have been separating the dates manually, calculating the weekly average and then copying the average in the column beside the week (Col J.)
Manually separating the data is of course tedious so could there be a way of doing this efficiently?

Attachment 293134

Thank you,
Marie-Louise

[RobertMika]

Try to use WEEKNUM() function to extrat the number of the week in a year.

[Fotis1991]

Let's hope that i get your goal corectly. In J2 and copy down.

Please Login or Register  to view this content.

[marielouise1]

Thank you! Both options did speed it up:
My question probably wasn't clear though as I would still have to manually separate the weeks

07-Nov-13
04-Oct-13
04-Oct-13
08-Oct-13
06-Oct-13
22-Oct-13
07-Oct-13
12-Nov-13
08-Oct-13
13-Oct-13
08-Oct-13
08-Oct-13
08-Oct-13
10-Oct-13
11-Oct-13
08-Oct-13
22-Oct-13

If I I wanted them to see how many were in the week beginning 07-Oct-13 would I have to manually sort and then count or canI use a formula that says 'If A1 is between 07/10/13 and 14/10/13 then record average for all dates within that week in cell B1'

- Not making sense is it? Haha

[Fotis1991]

..or canI use a formula that says 'If A1 is between 07/10/13 and 14/10/13 then record average for all dates within that week in cell B1'

That is what AVERAGEIFS does!

[RobertMika]

If you need to see week number
Sample%20Data(1).xlsx

[marielouise1]

Ah!
Got my head around it (changed it to the range on my original data)

Helped a lot, thanks!