Google Sheets formula for summing/averaging with specific conditions

Question

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.

Answer 1

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))