Forum Discussion

nickpant's avatar
nickpant
Copper Contributor
Oct 29, 2019

Averaging data

hello,   I am new in this forum. I want to average every 143 cell from a raw data which has around 300000 cells. Is any way to do it without writting the command every 143 cells?  Thank you for yo...
excel
office 365
training
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 02, 2019

nickpant 

I picked up on Wyn Hopkins problem and worked it through with dynamic arrays.  

In place of helper ranges, I used named formulas for the block index b, a sequence index s within each block and weighting w of 1/N for the weighted sum of each block.

= SEQUENCE( QUOTIENT(ROWS(Amounts), N) )

= SEQUENCE(1, N)

= SEQUENCE(N,1,1,0) / N

Using these, I first turned the list into a crosstab 

= INDEX( Amounts, s + N*(b-1) )

 

To average across each row requires MMULT [ this matrix multiplication is the only function that I know that will operate over the rows of a 2D array and return a column of results - Microsoft please do something about this! ].  This final step is achieved by nesting the crosstab to give the result

= MMULT( INDEX( Amounts, s + N*(b-1) ), w )

 

 The output comprises the list of block numbers with the associated averages adjacent.

 

Resources