5

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? – Jacob Jan Tuinstra Mar 14 '14 at 7:23
  • 1
    Given I answered my own question in the end, yes :) – THEMike Mar 18 '14 at 21:01
  • LOL, way to go dude(tte) – Jacob Jan Tuinstra Mar 18 '14 at 21:22
3

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 :)

1

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))

Noticed this while trying to accomplish nearly the exact same thing as you.

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.