Smooth data/moving average

Hello. I am having trouble smoothing or averaging data. Say I have data in A1 through A1000. I would like to make B1 the average of A1 - A5, B2 the average of A6-A10 and so on. Seems like it should be a simple series fill, but it won't work. Any suggestions? Thanks!

try in B1

=AVERAGE(INDEX(A:A,(ROW()*5)-4):INDEX(A:A,ROW()*5))

copy down

Originally Posted by scottjn

I would like to make B1 the average of A1 - A5, B2 the average of A6-A10 and so on.

teylyn has provided a solution to exactly what was requested but I am wondering if this is really what you need. What you have described is not a moving average. A moving average would be to make B1 the average of A1:A5, B2 the average of A2:A6 and so on.

I don't presume to tell you what is appropriate for your situation (since I don't actually know what you're doing ) but just wanted to take a moment to think about that. Your approach will certainly provide a degree of data smoothing (data clumping, really) but is not the conventional approach.

Originally Posted by scottjn

Thanks teylyn, that's just what I needed. Two related questions, when I pasted that equation my B1 ends up being the average of A6-A10 and not A1-A5, did I paste something wrong? Also, if I wanted it to be the average of every 10 values, instead of 5, how would I change it?

I did this an it worked perfectly, not sure what is causing your problem. I suggest you paste this formula into C1 if it's free, to debug what's going on. It will show you what row numbers are being used, based on the first formula.

="Row "&ROW()*5-4&" to " &ROW()*5

To clump into groups of 10 values change the formula to

=AVERAGE(INDEX(A:A,(ROW()*10)-9):INDEX(A:A,ROW()*10))

and the debug formula to

="Row "&ROW()*10-9&" to " &ROW()*10

For a general solution that makes it easier to tune, put the clump size into D1, for example, and use

=AVERAGE(INDEX(A:A,(ROW()*$D$1)-$D$1+1):INDEX(A:A,ROW()*$D$1))

If your data starts in row 2, you can take teylyn's formula (or mine modified, for groups of 10) and put the same formula in B2. If you want to put that formula in B1 you would modify it to be

=AVERAGE(INDEX(A:A,(ROW()-1)*10-8):INDEX(A:A,ROW()*10+1))


The more general formula, when putting your clump size into D1, is


=AVERAGE(INDEX(A:A,(ROW()-1)*$D$1+2):INDEX(A:A,ROW()*$D$1+1))

(Note that I made an error in the original which I corrected in that post.)

Here's how the formula breaks down:

ROW() <- gives the row number of the cell containing the formula


Suppose you have a clump size of 10. Now you have to use this row number to map your current row to a clump of 10 rows. Suppose you are going to put your formula into B1. If you multiply the row by 10 then subtract 10 and add back 2, you get the starting row of the corresponding clump. So if your formula is in row 1, substitute 1 where you see ROW() and you get

(1-1)*10+2 = 2


Now you need the ending row. Doing the same thing for the second part of the formula gives you

1*10+1 = 11

Similarly, copying the formula to row 2 gives

(2-1)*10+2 = 12
2*10+1 = 21

Now that we can determine the starting and ending rows of the range, we use the INDEX function to turn that information into a cell reference. The first argument of INDEX specifies a range. The second argument tells it which row in that reference we want. There is a third argument that tells the column but we allow it to default to 1 (there is only one column in A:A anyway). So now we have built our range using the two INDEX calls, and simply take the average of that range.