Is there a way to average specific ranges from a 2D matrix using an index value?-CodePudding

I have several matrixes that are 5 columns by 30 rows where data is sequential from row-to-row from left to right.

Example of ordering

I am looking to be able to average specific ranges from that data based on an index. For example, I'd like the averages of:

The first 6 values (yellow)
The next 3 values (green)
The next 2 values (blue)
The next 4 values (orange)

In my mind, one way to go about that is to flatten the array to a 2D vector, and then use something like an =average(offset(reference,X,0,Y,1)), where:

X would be the number of rows to skip in the vector
Y would be the row size of values I want to average

Is there some way to use offset, or some other Excel function to treat a matrix like a 2D vector and extract averages based on an index asking for the first X number of values, then the next Y, next Z, .... etc. values?

CodePudding user response：

With Office 2016 we will need to create a 1D array from the 2D array. We can do that with INDEX using MOD and INT to get the correct values based on the inputs of start and end.

=AVERAGE(
    INDEX(A1:E3,
        N(IF({1},INT((ROW(INDEX($ZZ:$ZZ,H1):INDEX($ZZ:$ZZ,H2))-1)/5) 1)),
        N(IF({1},MOD(ROW(INDEX($ZZ:$ZZ,H1):INDEX($ZZ:$ZZ,H2))-1,5) 1))))

H1 holds the start index and H2 the end index. Make sure to replace the A1:E3 with your range of the data set. the use of ZZ is arbitrary as we only care about the row numbers being returned. I use it because it usually is empty and will not cause a unneeded calc if the data changes.

With older versions this may require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode.

For those with Office 365 or later we can use TOCOL and SEQUENCE:

=LET(
    rng,A1:E3,
    st,H1,
    end,H2,
    AVERAGE(INDEX(TOCOL(rng),SEQUENCE(end-st 1,,st))))