Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
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
up vote 4 down vote accepted

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:


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.

share|improve this answer
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) – altCognito 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)... – altCognito Sep 20 '10 at 18:03

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.


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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .