Google-sheets – Create a 7 Day Running Total and Running Average

google sheets

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?

Best Answer

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

Related Solutions

Google Sheets Average – How to Autofill the ‘Average’ Function in Google Sheets

Use the following notation:

=AVERAGE(A$1:A5)

Copying this formula, will yield:

=AVERAGE(A$1:A7)

Absolute references explained:

$A$1; Both the column and row reference are fixed.
$A1; Only the column reference is fixed.
A$1; Only the row reference is fixed.

Reference: Absolute reference

Google Sheets – How to Drag Down ‘A1:A2’, ‘A1:A3’, ‘A1:A4’

You don't do this by increasing the formula by a cell for each row. This is not possible.

Instead, you use as the formula

cell B1 = A1
cell B2 = SUM(B1, A2)
cell B3 = SUM(B2, A3)

Now you can drag down the formula from B2 and B3 (don't include B1 in the selection!) for as many rows as you like.

Best Answer

Related Solutions

Google Sheets Average – How to Autofill the ‘Average’ Function in Google Sheets

Google Sheets – How to Drag Down ‘A1:A2’, ‘A1:A3’, ‘A1:A4’

Related Topic