Count week days vs. straddling weeks

[bart12117]

Hi there!

I'm working with data, which is grouped by weeks (no daily split available but result for every day within particular week is equal, no daily fluctuations).
I'm trying now to create a monthly average, which would be based on calendar months, from the 1st till 31st of a month. My problem comes from the straddling weeks. What would be the best way to calculate a monthly average score, where the formula will recognise the straddling weeks, divide them between two months and look only at the results from particular calendar month?

Based on the above, I'm looking for an average of:
- 3 days with score 9
- rest of the month with score 4


Thanks for your help!

 

[CharlesChuckieCharles]

Just to clarify, you assume that dates 29/08/23 through 31/08/23 are averaging 9 (not 4) since that average by the end of that week is 4 (04/09/23) so some how the week starting the 29th Aug is averaging 4 despite having 3 9's in it which is 27 divide 27 by 7 = 3.86 so effectively the other 4 days that week were 0.25 in order to have an average of 4

so do you actually need the Daily score rather than the weekly to get your required granularity?