I think you explained ok given NBVC's solution, however, sample files always help.
Looking at your Sheet3 you can adapt NBVC's solution to your own references:
Sheet3!H7:
=IF(MOD(ROWS(H$4:H7),4),"",AVERAGE(MMULT(OFFSET(C4,0,0,3,4),{1;1;1;1})))
copied down
However, given you have the Aggregate for each row already created you can simplify:
Sheet3!H7:
=IF(MOD(ROWS(H$4:H7),4),"",AVERAGE($G4:$G6))
copied down
Regards Col I - I would in turn modify that to:
Sheet3!I7:
=IF($H7="","",H7*3/2)
copied down
Then regards Sheet1
Sheet1!F7:
=INDEX(Sheet3!$I$4:$I$100,4*ROWS(F$7:F7))
copied down
note: Sheet1 formula assumes candidates are listed per Sheet3 - if not you will need to revise to an INDEX/MATCH type approach (or VLOOKUP) with appropriate row offset.
Bookmarks