Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a Google Spreadsheet tracking figures day by day. I want to add a column with a 7 day running total (sum of last 6 rows plus current row) and a column with a 7 day running average (average of last 6 rows plus current row).

I've managed to create a running total fine (Say numbers in column A, then B1 = A, B2 = B1 + A2 and drag the formula down to copy), but can't work out a 7 day running total.

Suggestions?

share|improve this question
    
Did you find the answer given useful? –  Jacob Jan Tuinstra Mar 14 at 7:23
1  
Given I answered my own question in the end, yes :) –  THEMike Mar 18 at 21:01
    
LOL, way to go dude(tte) –  Jacob Jan Tuinstra Mar 18 at 21:22
add comment

1 Answer 1

up vote 3 down vote accepted

Looks like I gave in too fast.

7 day running total: =Sum(Filter(D2:D8,A2:A8<=A8,A2:A8>A8-7))

Filter returns a range of data, filtered based on multiple (can have more than 2 conditions). In the case I'm returning D2:D8 (my count column) filtered where A2:A8 (my date column) is less than the current date (A8) and greater than the current date - 7 days. Stick this in the 7th cell, and drag the cell out to manipulate the formulae into the following cells.

Then for 7 day running average (assuming the above in F) G8 = F8 / 7 :)

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.