0

How would I do a rolling average over a row of dates and values?

The data basically looks like the following:

9/15/2010   199.8
9/14/2010   202
9/13/2010   200.8
9/12/2010   202.8
9/11/2010   201.4
9/10/2010   201.6
9/9/2010    201.8
9/8/2010    202.2
9/7/2010    202.2
9/6/2010    202.6
9/5/2010    205.8
9/4/2010    204.2
9/3/2010    205
9/2/2010    206.6
9/1/2010    208
8/31/2010   209.8
8/30/2010   210.2
8/29/2010   209.6
8/28/2010   209.6
8/27/2010   209.4
8/26/2010   209.8
8/25/2010   209.2
8/24/2010   210.8
1
  • 1
    are you looking for an average beside every row, or an average somewhere else that automatically adjusts as you add/remove/change data? – JNK Sep 15 '10 at 17:33
4

Assuming your first piece of data is in B1, you can copy and paste the following formula down the length of your data column. Put this in C2:

=AVERAGE($B$1:B2)

The dollar signs "anchor" the first cell, making it an absolute reference that doesn't move as you copy/paste the formula, while the second cell is a relative reference that will continue to update the average with each entry.

4
  • Hrm, I was honestly hoping for something simpler than a copy paste of the formula down the line. (not that this isn't what I asked for, it is) – cgp Sep 15 '10 at 17:26
  • @alt You don't get much simpler than one formula you just copy down the entire column, this is the way I'd do it. +1 @Michael. – DMA57361 Sep 15 '10 at 21:21
  • You shouldn't be copying and pasting that. Enter the first one, select the whole column, then Edit→Fill→Down. – Stephen Jennings Sep 15 '10 at 21:23
  • Using the fill function is what I called a "copy paste". Each cell is evaluated individually and could potentially be different. (though it wouldn't be)... – cgp Sep 20 '10 at 18:03
2

By "Rolling Average", I believe you are talking about the average of the last n days (say 5 days).

(Base on the data you provided, assuming they are column A & B)

Enter =AVERAGE(OFFSET(B1,0,0,5)) into C1, then copy and paste it downwards (if necessary).

C1 should output 201.36 which is the average of the recent 5 days; C2 is 201.72, and so on.


Explaination:

OFFSET() outputs a range starting from B1 with height of 5 (downwards, including B1 itself).
(Replace 5 with the number of days you want.)

Then, AVERAGE() is used to compute the average of the range.

1
=AVERAGE($B$1:INDEX($B$1:$B$100,COUNTA($B$1:$B$100)))

This will update as you add data. But note that it only looks at the first 100 rows, so adjust if there's going to be more. Also, if you insert lines at the top, the references will shift down.

0

Having reviewing my own answer years later, I would like to suggest a simpler formula.

  1. Input into C1 =average(B1:B5) (assume 5-day rolling average)
  2. Copy and paste downwards in column C

Note: For the earliest record (8/24/2010), the rolling average (column C) will show the value of that day. For the second record (8/25/2010), it will show the average of first 2 days, and so on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.