Sum values for a month from weekly data weighted by day

1
votes

I have a sheet with weekly values, and I'd like to calculate what the monthly value is. The catch is that if a week's value say 70 is split across two months (say 3 days in January and 4 days in February) - I'd like the calculated (summed) value for January to take the value for the week and (3/7) x 70 = 30 for January and (4/7) x 70 = 40 summed for February.

Example sheet here: https://docs.google.com/spreadsheets/d/19LClrTXihPfwRtvX7nrOfKpz7aLC-RrczpED-U8ufmo/edit#gid=0

Any help on a formula to do this would be greatly appreciated.

1
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language
sheet is private – player0
Sorry opened it – SQLearner

1 Answers

1
votes
=ARRAYFORMULA(REGEXREPLACE(TO_TEXT(QUERY({TEXT(TRANSPOSE(D1:1), "m♦mmmm yyyy"), 
 TRANSPOSE(D2:2)-IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))), 4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )+
 QUERY({""; IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))),4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )}, 
 "limit "&COLUMNS(D1:1), 0)}, 
 "select Col1,sum(Col2) where not Col2 = 0 group by Col1 label sum(Col2)''", 0)), 
 "(.+)♦", ))

0

FIX:

=ARRAYFORMULA(TRANSPOSE(QUERY({REGEXREPLACE(TO_TEXT(QUERY({TEXT(TRANSPOSE(D1:1), "m♦mmmm yyyy"), 
 TRANSPOSE(D2:2)-IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))), 4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )+
 QUERY({""; IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))),4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )}, 
 "limit "&COLUMNS(D1:1), 0)}, 
 "select Col1,sum(Col2) where not Col2 = 0 group by Col1 label sum(Col2)''", 0)), 
 "(.+)♦", ), 
 IF(LEN(QUERY(REGEXREPLACE(TO_TEXT(QUERY({TEXT(TRANSPOSE(D1:1), "m♦mmmm yyyy"), 
 TRANSPOSE(D2:2)-IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))), 4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )+
 QUERY({""; IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))),4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )}, 
 "limit "&COLUMNS(D1:1), 0)}, 
 "select Col1,sum(Col2) where not Col2 = 0 group by Col1 label sum(Col2)''", 0)), 
 "(.+)♦", ),"select Col1",0)), MONTH(LEFT(
 QUERY(REGEXREPLACE(TO_TEXT(QUERY({TEXT(TRANSPOSE(D1:1), "m♦mmmm yyyy"), 
 TRANSPOSE(D2:2)-IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))), 4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )+
 QUERY({""; IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))),4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )}, 
 "limit "&COLUMNS(D1:1), 0)}, 
 "select Col1,sum(Col2) where not Col2 = 0 group by Col1 label sum(Col2)''", 0)), 
 "(.+)♦", ),"select Col1", 0), 3)&1)&RIGHT(
 QUERY(REGEXREPLACE(TO_TEXT(QUERY({TEXT(TRANSPOSE(D1:1), "m♦mmmm yyyy"), 
 TRANSPOSE(D2:2)-IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))), 4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )+
 QUERY({""; IF(MOD(ROW(INDIRECT("A1:A"&COLUMNS(D1:1))),4)=0, 
 DAYS(EOMONTH(TRANSPOSE(D1:1), 0), TRANSPOSE(D1:1))/7*TRANSPOSE(D2:2), )}, 
 "limit "&COLUMNS(D1:1), 0)}, 
 "select Col1,sum(Col2) where not Col2 = 0 group by Col1 label sum(Col2)''", 0)), 
 "(.+)♦", ),"select Col1", 0), 4), )*1}, 
 "select Col1,Col2 order by Col3")))

0