Google Sheets: Moving daily, weekly, monthly, and yearly averages

Question

I have a spreadsheet where I'm collecting data, and entering a date the data is collected. I would like to get the moving averages of collected data in my spreadsheet on a daily, weekly, monthly, and yearly for charts.

The two columns that I'm working off of are "Date" in column A (when the data was collected) and "Data" in column C (the actual collected data). The date is always increasing and is "mm/dd/yyyy" format. The data in column C are integers, and an almost-always increasing running total, except in four places where manual corrections had to be made.

The collected data is not entered every day, and as such, there are gaps between dates in the "Date" column. Sometimes 2 or 3 days go by without collected data, sometimes more. The largest gap is 98 days without collected data.

E.G.:

    + ---------- + - + ----- +
    |     A      | B |   C   |
+ - + ---------- + - + ----- +
| 1 |    Date    |   | Data  |
| 2 |  6/15/2016 |   | 1263  |
| 3 |  6/30/2016 |   | 1371  |
| 4 |   7/1/2016 |   | 1382  |
| 5 |   7/7/2016 |   | 1429  |
| 6 | 10/13/2016 |   | 2588  |

I have collected almost 3 years of data across 217 rows.

Getting a moving daily average seems as simple as =(C3-C2)/DATEDIF(A2, A3, "D")). Getting the moving weekly, monthly, and yearly averages are stumping me.

How can I get moving weekly, monthly, and yearly averages for data that isn't entered every day in Google Sheets?

well, can you atleast provide an example how your desired output should look like? — player0
Essentially, I want to know what the sliding window data differences are across each day, every 7 days, every 30 days, and every 365 days. For example, the date difference between A2 and A3 in my post is 15 days. The data difference is 108 units. That's about 7.2 data units per day, or about 50.2 data units per week. I would expect the weekly differences chart to be less spiky than the daily, and the monthly less spiky than the weekly, and the yearly less spiky than the monthly. — Aaron Toponce
You need to interpolate between points. Before you try to make a quick formula that gives the result, make sure you understand what the actual values would be if you did have data logged daily: make an expanded table with data for each and every day in the period, and come up with the formulas & results for your averages from that dataset. Share your resulting formulas and what you think the next step is after that. — tehhowch
question is how you imagine 7 days... 7 days between given dates or 7 days regardless of every day or 7 days between measurements — player0

player0 player0 · Accepted Answer · 2019-03-02T22:26:26

G2:

=IFERROR(MINUS(QUERY($A2:$C, 
 "select C 
  where A >= date'"&TEXT($A2,   "yyyy-mm-dd")&"' 
    and A <= date'"&TEXT($A2+7, "yyyy-mm-dd")&"'
  limit 1 offset "&COUNTA(QUERY($A2:$C, 
 "select C 
  where A >= date'"&TEXT($A2,   "yyyy-mm-dd")&"' 
    and A <= date'"&TEXT($A2+7, "yyyy-mm-dd")&"'"))-1), $C2)/7, )

H2:

=IFERROR(MINUS(QUERY($A2:$C, 
 "select C 
  where A >= date'"&TEXT($A2,    "yyyy-mm-dd")&"' 
    and A <= date'"&TEXT($A2+30, "yyyy-mm-dd")&"'
  limit 1 offset "&COUNTA(QUERY($A2:$C, 
 "select C 
  where A >= date'"&TEXT($A2,    "yyyy-mm-dd")&"' 
    and A <= date'"&TEXT($A2+30, "yyyy-mm-dd")&"'"))-1), $C2)/30, )

I2:

=IFERROR(MINUS(QUERY($A2:$C, 
 "select C 
  where A >= date'"&TEXT($A2,     "yyyy-mm-dd")&"' 
    and A <= date'"&TEXT($A2+365, "yyyy-mm-dd")&"'
  limit 1 offset "&COUNTA(QUERY($A2:$C, 
 "select C 
  where A >= date'"&TEXT($A2,     "yyyy-mm-dd")&"' 
    and A <= date'"&TEXT($A2+365, "yyyy-mm-dd")&"'"))-1), $C2)/365, )

Google Sheets: Moving daily, weekly, monthly, and yearly averages

2 Answers