Formula to calculate monthly values from weekly time series, using days in each week

[axf80]

I want to calculate monthly values from weekly ones, by taking into account the number of days in the weeks that overlap each month.

Below is an example. Row 5 shows the figures I want to calculate, by referencing the rows above.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]03/05/2015[/TD]
[TD]10/05/2015[/TD]
[TD]17/05/2015[/TD]
[TD]24/05/2015[/TD]
[TD]31/05/2015[/TD]
[TD]07/06/2015[/TD]
[TD]14/06/2015[/TD]
[TD]21/06/2015[/TD]
[TD]28/06/2015[/TD]
[TD]05/07/2015[/TD]
[TD]12/07/2015[/TD]
[TD]19/07/2015[/TD]
[TD]26/07/2015[/TD]
[TD]02/08/2015[/TD]
[TD]09/08/2015[/TD]
[TD]16/08/2015[/TD]
[TD]23/08/2015[/TD]
[TD]30/08/2015[/TD]
[TD]06/09/2015[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]17[/TD]
[TD]22[/TD]
[TD]18[/TD]
[TD]21[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]22[/TD]
[TD]20[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]30[/TD]
[TD]32[/TD]
[TD]34[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May-15[/TD]
[TD]Jun-15[/TD]
[TD]Jul-15[/TD]
[TD]Aug-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]72.42857[/TD]
[TD]98.285714[/TD]
[TD]91.85714[/TD]
[TD]132.1429[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So far I've been using a formula like this for July-15 for example:

=(J2/7*5)+K2+L2+M2+(N2/7*5)

It's taking 5/7 of the values for first and last weeks, because 5 of their 7 days were in July.

However, I want a formula that will do this automatically, simply by referencing the weekly time series, and the month in question. It therefore wouldn't require me to manually change the number of weeks/days depending on the particular month.

Hope that makes sense - really appreciate any help, this has me stuck.

 

[Rijnsent]

Hi axf80,

it's solvable with just formulas, but I'm not sure the end result will make you happy, as the end result is quite a long array formula. Taking it step-by-step (as that is how I got to this formula). My formulas are with ; as the comma is my decimal symbol, it might be that you need to change the ; for commas:

For your weekly numbers, 21/06/2015 means: the date range 15/06 to 21/06 (including those dates). This has to be compared with the date range of the month: Jun-15 (probably a date, 01/07/2015) means 01/07 to 31/07 (including). So step 1:
week_end: 21/06 (fixed value) , week_start = week_end - 6
month_start: 01/07/2015, month_end = DATE(YEAR(month_start);MONTH(month_start)+1;0)
What I did in an example-file is to add those elements as "helper rows", inserting row 2 and a row below the row with the months (May-15 etc). You can opt to squeeze it all into the one formula, but that's overcomplicating things.

Okay, step 2:
The number of days of that week in a month, I started with one month as an example, the formula starts with the DAYS formula:
=DAYS(IF(week_end>month_end;month_end;week_end);IF(week_start>month_start;week_start;month_start))+1
If you calculate that formula for all the weeks and one month, you'll see a positive number of days/week in calculated month and negative numbers in the other months, so it needs a correction for that:
=IF(week_start>=month_start;IF(week_end<=month_end;DAYS(IF(week_end>month_end;month_end;week_end);IF(week_start>month_start;week_start;month_start))+1;0);0)

That formula calculates the amount of days in a certain week that fall in a month.

Step 3:
Okay, that was the nasty bit, the next step is a SUMPRODUCT formula to multiply the number of days per week with your value and divide it by 7.
For cell A7: {=SUMPRODUCT(($A$3:$S$3)*(1/7)*(IF($A$1:$S$1>=A$5;IF($A$2:$S$2<=A$6;(DAYS(IF($A$1:$S$1>A$6;A$6;$A$1:$S$1);IF($A$2:$S$2>A$5;$A$2:$S$2;A$5))+1);0);0)))} (array formula: use CTRL+SHIFT+ENTER.
This assumes your data: row 1 your week_end days, row 2: the week_start days, row 3: the values, row 4: empty, row 5: start of month, row 6: end of month
If you don't want the extra rows, you'd end up with something like this for cell A7:
{=SUMPRODUCT(($A$3:$S$3)*(1/7)*(IF($A$1:$S$1>=A$5;IF($A$1:$S$1-6<=DATE(YEAR(A$5);MONTH(A$5)+1;0);(DAYS(IF($A$1:$S$1>DATE(YEAR(A$5);MONTH(A$5)+1;0);DATE(YEAR(A$5);MONTH(A$5)+1;0);$A$1:$S$1);IF($A$1:$S$1-6>A$5;$A$1:$S$1-6;A$5))+1);0);0)))} (this does assume you inserted the two rows but don't want to use them)

Hope that works for you too,

Koen

 

[axf80]

Hi Koen,

Many thanks for your response. I'm very happy to use a long array formula if it will get the job done

I was unable to get your formula to work, because I don't have the function DAYS in my version of Excel. However, by slightly modifying the formula to the one below I was able to make it work:
{=SUMPRODUCT(($A$3:$S$3)*(1/7)*(IF($A$1:$S$1>=A$5,IF($A$1:$S$1-6<=DATE(YEAR(A$5),MONTH(A$5)+1,0),(((IF($A$1:$S$1>DATE(YEAR(A$5),MONTH(A$5)+1,0),DATE(YEAR(A$5),MONTH(A$5)+1,0),$A$1:$S$1))-(IF($A$1:$S$1-6>A$5,$A$1:$S$1-6,A$5)))+1),0),0)))}

Thanks again for your help, I wouldn't have been able to figure this out on my own!

 

[Rijnsent]

Hi axf,

cool! I just found that the DAYS formula is new in Excel 2013, so that might be why . Generally the trick with this kind of beasts is to break it down into little steps, starting with the smallest block. But even then it takes a lot of trial and error (I first had a MAX function around the DAYS part to filter out the negative numers, but found that inside an array-SUMPRODUCT that doesn't work).

Happy Excelling ,

Koen