# How to average every n elements in an array

1. ## How to average every n elements in an array

Hi,
I have an array consisting of values from a named range. What I would like to do is to calculate an average value every n elements and save it into a new array.
The original array is created by the code given below. ``Please Login or Register  to view this content.``
Regards  Register To Reply

2. ## Re: How to average every n elements in an array

Do you want an average of every nth row, e.g. AVERAGE(B3, B6, B9, B12, ...

Or you you want to break the range into blocks, each of which is N rows, and average each of those blocks AVERAGE(B3:B6), AVERAGE(B7:B10), ....  Register To Reply

3. ## Re: How to average every n elements in an array

Hi,
The second approach. I want to take n rows in an array, average them, then take another n rows, average them and so on.

Regards  Register To Reply

4. ## Re: How to average every n elements in an array

Here is a formula based approach that employs three helper columns which may be moved and/or hidden for aesthetic purposes:
1. The first helper column (F) is populated using: =IF(MOD(ROWS(A\$1:A1),C\$1)=1,1,"")
2. The second helper column (G) is populated using: =COUNTIFS(F\$2:F2,1)
3. The third helper column (H) is populated using: =AVERAGEIFS(A\$2:A\$13,G\$2:G\$13,G2)
4. Cell C1 is manually populated with n
5. D2:D13 are populated using: =IF(ROWS(A\$1:A1)>COUNT(A\$2:A\$13)/C\$1,"",ROWS(A\$1:A1))
6. E2:E13 are populated using: =IF(D2="","",INDEX(H\$2:H\$13,MATCH(D2,G\$2:G\$13,0)))
Note that in the attached file columns F:H are hidden by choosing font color white
Let us know if you have any questions.  Register To Reply

5. ## Re: How to average every n elements in an array

@ JeteMC,
Thank you, but the "worksheet formula" approach is not what I would like to use. At present, in order to calculate the average values, and thus reduce the data points, I use the following code: ``Please Login or Register  to view this content.``
It is based on the nested formula using Average and Offset functions, in accordance to this web page. However this approach makes everything work slowly, that is why I want to use an array and reduce the data points in flight, without writing formulas directly into the worksheet cells.
Can you advise on how to do that?

Regards  Register To Reply

6. ## Re: How to average every n elements in an array

Can you post an example file so we have something to work with.

On how to, follow the instructions in the yellow banner at the top of the page  Register To Reply