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?

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

2 Answers 2

Reset to default
4

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

Improve this answer
0
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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