Calculate rolling average based on date range

Question

I have a google form I use every morning to weigh myself

Column 1 is the auto-generated timestamp. Weight is the value I inputted

Timestamp   Morning weight
7/1/2021 8:41:55    185
7/2/2021 5:50:54    185.8
7/4/2021 8:16:58    186.2
7/5/2021 9:45:23    184
7/6/2021 5:56:06    184.2
7/7/2021 6:52:24    183.4
7/8/2021 4:37:18    184.8
7/9/2021 5:46:20    183.8
7/10/2021 10:07:58  184.2
7/11/2021 9:07:25   182.4
7/12/2021 6:39:53   182.8
7/14/2021 5:36:02   183.4
7/15/2021 6:14:42   181.2
7/16/2021 5:30:41   182.6
7/18/2021 7:00:43   181
7/19/2021 5:14:21   180.6
7/22/2021 5:54:42   180.2
7/23/2021 5:46:52   179.4
7/24/2021 8:14:06   180.8
7/25/2021 6:58:56   180.4
7/29/2021 7:25:15   180.8
7/30/2021 6:09:04   180.4
7/31/2021 7:34:20   180
8/1/2021 8:35:40    179.6
8/2/2021 5:48:48    178
8/3/2021 5:36:37    179.4
8/5/2021 5:53:58    179.8
8/6/2021 5:43:34    179

I'd like to calculate what my average weight is for each week. Some days I forget to input data, so I'd like to calculate based on whatever is available:

7/19/2021    181.76 // calculated from 180.6 + 181 + 182.6 + 181.2 + 183.4` / 5
7/26/2021    180.2 // calculated from 180.4 + 180.8+179.4 + 180.2` / 4

Here's a spreadsheet image that might be helpful. I color coded what range-values I want based on the dates

stuff I've tried / was doing before

I tried using a VLOOKUP formula, but I don't think that has the ability to add a rolling calculation. INDEX/MATCH seems viable too but not sure how that works

Before I just inputted another column next to the raw data, and ran this

=ROUND(AVERAGE('Daily Measurements'!B89:B96),1)`

then ran a corresponding VLOOKUP.

but I need one that's more robust though

I'm currently calculating the each monday's date based on a PreviousDate + 7 formula

Answer 1

try:

=INDEX(QUERY({WEEKNUM(A2:A, 2), B2:B},
 "select Col1,avg(Col2)
  where Col2 is not null 
  group by Col1 
  label Col1'week number'"))

update

rounded to 1 decimal place:

=INDEX(TEXT(QUERY({WEEKNUM(A2:A, 2), B2:B},
 "select Col1,avg(Col2)
  where Col2 is not null 
  group by Col1 
  label Col1'week number'"), {"@", "0.0"}))

or:

=INDEX(TRUNC(QUERY({WEEKNUM(A2:A, 2), B2:B},
 "select Col1,avg(Col2)
  where Col2 is not null 
  group by Col1 
  label avg(Col2)''"), 1))

or:

=INDEX(ROUND(QUERY({WEEKNUM(A2:A, 2), B2:B},
 "select Col1,avg(Col2)
  where Col2 is not null 
  group by Col1 
  label avg(Col2)''"), 1))