+ Reply to Thread
Results 1 to 6 of 6

How to average every n elements in an array

  1. #1
    Registered User
    Join Date
    01-12-2015
    Location
    PL
    MS-Off Ver
    2016
    Posts
    13

    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.
    Can you please advise on how to do that?
    The original array is created by the code given below.
    Please Login or Register  to view this content.
    Thank you in advance
    Regards

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,151

    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), ....
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    01-12-2015
    Location
    PL
    MS-Off Ver
    2016
    Posts
    13

    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

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,639

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    01-12-2015
    Location
    PL
    MS-Off Ver
    2016
    Posts
    13

    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

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    6,312

    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
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1