Averaging data

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.

nickpant 

If you actually want to average every day then insert a pivot table.

"Date and Tme" in rows area.

Group "Date and Tme" into days, months and years.

"Average Wind Speed, m/s" into values area.

Change the field settings to "Average".

The problem with your data is that your first day only hast 123 values.

And there are more days with number of values different from 144.

Hi nickpant 

Probably not the most elegant solution but I'm a fan of helper columns to make things easier to understand

You would just change the yellow 10 to 143

Hi nickpant 

As per my understanding you need to get average with the gap of 143 rows, if this is the case you can use below formula:

=AVERAGE(OFFSET($C$5:$C$300004,((ROW(C5)-ROW($C$5))*143),0,1,1):OFFSET($C$5:$C$300004,

((ROW(C5)-ROW($C$5))*143),0,143,1))

A sample file is also attached for your reference, please let me know if it works for you.

Thanks

Tauqeer