I am hoping for a formula to take hours from the name columns and sum/average them by week, into a separate table like the 2nd one below. The formulas need to update upon changing the start and end week cells.
| Body Part | Start Week | End Week | Arnold (hours) | Usain (hours) | Bob (hours) |
|---|---|---|---|---|---|
| Arms | 1 | 3 | 6 | 3 | 0 |
| Legs | 1 | 6 | 12 | 36 | 20 |
| Chest | 2 | 4 | 6 | 2 | 2 |
| Booty | 4 | 6 | 9 | 12 | 3 |
| Core | 1 | 5 | 10 | 5 | 5 |
Formula Needed:
| Hours | Arnold | Usian | Bob |
|---|---|---|---|
| Week 1 | 6 | 8 | 4.33 |
| Week 2 | 8 | 8.67 | 5 |
| Week 3 | 8 | 8.67 | 5 |
| Week 4 | 9 | 11.67 | 6 |
| Week 5 | 7 | 11 | 5.33 |
| Week 6 | 5 | 10 | 4.33 |
Bonus if there is a way to also quickly average hours by body parts if for example there are multiple Arms rows.
CodePudding user response：
try:
=ARRAYFORMULA(LAMBDA(a, b, QUERY(SPLIT(FLATTEN(BYCOL(D1:F1, LAMBDA(xx, FLATTEN(IF(
IF(a>=SEQUENCE(1, MAX(a)), "Week "&TEXT(SEQUENCE(1, MAX(a)) b, "00"), )="",,
REGEXEXTRACT(OFFSET(xx,,,1), "(. ) \(")&"×"&
IF(a>=SEQUENCE(1, MAX(a)), "Week "&TEXT(SEQUENCE(1, MAX(a)) b, "00"), )&"×"&
QUERY({REGEXEXTRACT(OFFSET(xx,,,1), "(. ) \("); OFFSET(xx,1,,9^9)/(a)}, "offset 1", )))))), "×"),
"select Col2,sum(Col3) where Col3>0 group by Col2 pivot Col1"))
(C2:INDEX(C:C, MAX(ROW(C:C)*(C:C<>"")))-B2:INDEX(B:B, MAX(ROW(B:B)*(B:B<>""))) 1,
B2:INDEX(B:B, MAX(ROW(B:B)*(B:B<>"")))-1))
