Use formula in intervals Excel-CodePudding

I want to get the average from different cells without manually selecting the cells.

For example:

I want to get the average of every 10 cells like this:

    =AVERAGE(A2:A11)
    =AVERAGE(A12:A21)
    =AVERAGE(A22:A31)

I have already tried the drag and drop of the formulas, however, I got the following result

AVERAGE(A2:A11) -> AVERAGE(A3:A12)

which is not the desired result.

Thank you!

CodePudding user response：

You could use something like:

=AVERAGE(INDEX(A:A,(ROWS($A$1:$A1)-1)*10 2):INDEX(A:A,(ROWS($A$1:$A1)-1)*10 11))

CodePudding user response：

Here is an option with ms365's new BETA-function WRAPROWS():

Formula in C2:

=BYROW(WRAPROWS(A2:A31,10),LAMBDA(a,AVERAGE(a)))

Or, if you want something to drag down:

=AVERAGE(INDEX(A:A,SEQUENCE(10,,(ROW(1:1)-1)*10 2)))

CodePudding user response：

You can use OFFSET() and some arithmetic based on the address of each cell.

For example, if your first formula is in row 1, you can use =AVERAGE(OFFSET($A$2:$A$11,10*(ROW()-1),0)) and fill down. If you're starting in a row other than 1, change the value you're subtracting from ROW() accordingly.