Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
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
add comment

3 Answers

up vote 3 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:

=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.

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
add comment
=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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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