How do you average repeating blocks of cells in Excel?-CodePudding

I am trying to figure out how to average 4 x 4 groups of cells in my spreadsheet across a very large data set. I've tried using OFFSET with a cell range (e.g. B2:E5) but I haven't had success (I don't even know if you can use a range for the reference with OFFSET). This is my first time tackling a problem like this, so any advice would be welcome! A portion of the data set is attached to give an idea of the ranges I would like to average.

CodePudding user response：

You can use INDEX to refer to a range derived from values fro TopLeft and BottomRight corners, in the form

INDEX(DataRange, TopRow, LeftCol):INDEX(DataRange, BottomRow, RightCol)

Then wrap that in AVERAGE(...)

To demonstrate

=AVERAGE(INDEX($B$7:$AH$903,B1,B2):INDEX($B$7:$AH$903,B3 B1-1,B4 B2-1))

Note: INDEX has the advantage over OFFSET in that it's