# Create a 7 Day Running Total and Running Average

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?

• Did you find the answer given useful? Mar 14, 2014 at 7:23
• Given I answered my own question in the end, yes :) Mar 18, 2014 at 21:01
• LOL, way to go dude(tte) Mar 18, 2014 at 21:22

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 :)
I think a better running average would basically use the same date filtering you did for your sum, but using average instead. Based on your sum function, I think your running seven day average would look something like this: =Average(Filter(F2:F8,A2:A8<=A8,A2:A8>A8-7))