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.
Thank you in advance
Please Login or Register to view this content.
Regards
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.
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
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.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
@ 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: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.Please Login or Register to view this content.
Can you advise on how to do that?
Regards
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.
Hi, sorry the late reply.
I've slightly altered my approach. Instead of trying to average every n rows in an array, I use Application.Average function with the "FOR" loop and write the results to an array, and subsequently i print that array in the predefined range - see the code below. This way the whole tool, I was working on is about 2-3 times faster, which for me is more than satisfying. Thank you everyone for your help.
Please Login or Register to view this content.
This is what I kind of already had prepared but was waiting for an example file to do final tests.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
@bakerman2, It was not my intention to make you feel bad
I will hold off with marking this thread as solved until next week. Tomorrow I will present the tool the people who ordered it, so chances are I will have to rework some of my ideas.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks