excel averaging every nth row and column blocks-CodePudding

I have a big data set which has more than 100k rows and 191 columns. i need to calculate every tenth row for all the columns. my data looks something like this:


1	2	5	6	7
3	4	10	9	8
1	2	5	6	7
3	4	10	9	8
1	2	5	6	7
3	4	10	9	8

how can i calculate the average for every nth row in excel

CodePudding user response：

With latest update of Microsoft 365 can try-

=BYROW(FILTER(A1:E24,MOD(ROW(A1:A24),10)=0),LAMBDA(a,AVERAGE(a)))

CodePudding user response：

as an aside 100k rows, 191 columns. a very large data set but probably still alittle surprised at 1.5gb.

depends where you want the averages but lets assume you want them in another cell B2 of another sheet and just an average in each row and that your current sheet is called Data:

=AVERAGE(OFFSET(Data!$A$1,(ROW(B2)-2)*10,0,10,191))

this is an average of all the columns

by the way unless you have a beast of a machine your computer will hate this! and I would suggest using VBA rather than this but it should work