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.
<tbody>
</tbody>
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.
Below is an example. Row 5 shows the figures I want to calculate, by referencing the rows above.
03/05/2015 | 10/05/2015 | 17/05/2015 | 24/05/2015 | 31/05/2015 | 07/06/2015 | 14/06/2015 | 21/06/2015 | 28/06/2015 | 05/07/2015 | 12/07/2015 | 19/07/2015 | 26/07/2015 | 02/08/2015 | 09/08/2015 | 16/08/2015 | 23/08/2015 | 30/08/2015 | 06/09/2015 |
15 | 13 | 14 | 17 | 22 | 18 | 21 | 27 | 26 | 22 | 20 | 19 | 20 | 24 | 25 | 30 | 32 | 34 | 30 |
May-15 | Jun-15 | Jul-15 | Aug-15 | |||||||||||||||
72.42857 | 98.285714 | 91.85714 | 132.1429 |
<tbody>
</tbody>
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.