Im trying to take the average of groups of 10 cells that are part of a data set that is ordered in 1 column but has some 15000 entries. Do I have to change "manually" the cells range in the formula
AVERAGE(A1:A10)
to
AVERAGE(A11:A20)
I tried with Data Analysis but it only changes
AVERAGE(A1:A10)
in to
AVERAGE(A2:A11)
How complicated would be to use a For Next loop in VBA code to indicate the range and "set" of data to calculate?
Thanks in advance
Sebastian
If you mean you want to find the average of each group of 10 cells, beginning at A1, then try:
=AVERAGE(INDEX($A$1:$A$10,1+((ROW(A1)-1)*10)):INDEX($A$1:$A$10,10+((ROW(A1)-1)*10))) copied down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Im not sure how this formula works, how do I tell excel to calculate the next group of cell (A11:A20)? And more importantly how do I tell excel to repeat this automatically up to the group of cells (A15321:A15330).
Thank you for help
That formula needs to be adjusted to reference your whole range.
you would enter this formula:=AVERAGE(INDEX($A$1:$A$15330,1+((ROW(A1)-1)*10)):INDEX($A$1:$A$15330,10+((ROW(A1)-1)*10)))
in a cell and then copy it down. Each cell will show the average of the next 10 cells. Copy it down until you get an error, then you will know you have past the last cell in the numbers range.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks