CODEWITHSUNDEEP
dategoogle-sheetsgoogle-sheets-apigoogle-sheets-query
JordanBelf
JordanBelf

Reputation: 3338

Finding the last seven days in a time series

I have a spreadsheet with column A which holds a timestamp and updates daily. Column B holds a value. Like the following:

+--------------------+---------+
| 11/24/2012 1:14:21 | $487.20 |
| 11/25/2012 1:14:03 | $487.20 |
| 11/26/2012 1:14:14 | $487.20 |
| 11/27/2012 1:14:05 | $487.20 |
| 11/28/2012 1:13:56 | $487.20 |
| 11/29/2012 1:13:57 | $487.20 |
| 11/30/2012 1:13:53 | $487.20 |
| 12/1/2012 1:13:54  | $492.60 |
+--------------------+---------+

What I am trying to do is get the average of the last 7, 14, 30 days.

I been playing with GoogleClock() function in order to filter the dates in column A but I can't seem to find the way to subtract TODAY - 7 days. I suspect FILTER will also help, but I am a little bit lost.

Upvotes: 0

Views: 1766

Answers (1)

AdamL
AdamL

Reputation: 24619

There are a few ways to go about this; one way is to return an array of values with a QUERY function (this assumes a header row in row 1, and you want the last 7 dates):

=QUERY(A2:B;"select B order by A desc limit 7";0)

and you can wrap this in whatever aggregation function you like:

=AVERAGE(QUERY(A2:B;"select B order by A desc limit 7";0))

Upvotes: 3

Related Questions