2

There's a question on this forum that asks how to get a 7 day running total and running average. It's available here and it reads like this:

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.

There's a handy solution by THEMike that solves the problem, but without ArrayFormula. My question is, how could I do the same thing using ArrayFormula?

Let's assume the data looks something like the following:

| Date   | Data | 7 Day Running Total | 7 Day Running Average |
|--------|------|---------------------|-----------------------|
| 6/1/15 | 3    |                     |                       |
| 6/2/15 | 4    |                     |                       |
| 6/3/15 | 4    |                     |                       |
3

This can be done by using the running total as an intermediate result (you can put in some column further on the right and/or make it hidden, if you don't want to see it).

I'll use column C for the cumulative total and column D for 7-day total; finding the average is simple division.

The formula for cumulative total is found here. I'll use it in this form

C2 =ARRAYFORMULA(IF(LEN(A2:A), SUMIF(ROW(B2:B),"<="&ROW(B2:B), B2:B), ""))

The conditional statement about LEN(A2:A) prevents output in rows where no data is present.

And here is the computation of 7-day running total:

D2 =ARRAYFORMULA(IF(LEN(A2:A), C2:C-IFERROR(VLOOKUP(A2:A-7, A2:C, 3), 0), ""))

The key player here is the VLOOKUP function which find the latest date that is less than or equal to "a week ago", and returns the cumulative total for it. This represents the total of "old" entries, which should be subtracted from the current total so that only the entries from the last 7 days are counted.

The IFERROR statement handles the case when there is nothing to subtract, i.e., all preceding dates are within the 7-day window.

Here is sample output to verify correctness:

+-----------+------+---------------+-------------+
|   Date    | Data | Running Total | 7-day total |
+-----------+------+---------------+-------------+
| 1/4/2015  |    5 |             5 |           5 |
| 1/5/2015  |    3 |             8 |           8 |
| 1/9/2015  |    1 |             9 |           9 |
| 1/11/2015 |    9 |            18 |          13 |
| 1/12/2015 |    4 |            22 |          14 |
| 1/13/2015 |    2 |            24 |          16 |
| 1/27/2015 |   64 |            88 |          64 |
+-----------+------+---------------+-------------+
  • This is a great solution. How would one know how many cells were selected (0 thru 7) in order to compute the 7 day average? – emmby Jul 6 '15 at 19:54
  • My understanding of "7 day average" was that it is a 7-day total divided by 7. If some days have no sales/transactions etc, that automatically counts as zero. – user79865 Jul 6 '15 at 19:56
  • But to answer your question, replacing SUMIF with COUNTIF should do it. – user79865 Jul 6 '15 at 19:57
  • it's divide by seven, except for the first six data points. – emmby Jul 7 '15 at 21:09

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.