# How to get weekly averages for workout tracker data in Google Sheets?

I am making a personal training app that uses data from Google Sheets. I want to create a formula that takes the data from a sheet of workout tracking inputs and creates a weekly average for each person.

Here is sample data and what the out put should look like in the end.

• Users will submit a form with their daily Weight, Protein, etc. and the data appears here.

• End goal is to group the daily logs into end of week date per user and calculate that weeks tracking average. It would look like this for the app to post the data correctly.

I have looked up formulas and tried different methods but being no expert, I need some guidance.

This formula gave me close to what I wanted. It counts the number of inputs per user per week.

=query( ArrayFormula({YEAR(A2:A),weeknum(A2:A),B2:C}), "Select Col1,Col2,Col3,count(Col3) group by Col1,Col2,Col3 label Col1'Year', Col2'Week',Col3'Item'" )

I thought changing count to avg would work, but I got a value error in parameter 2 AVG_SUM_NUMERIC only.

I also tried A2:A-WEEKDAY(A2:A,2)+6 to get the end week date but unsure of how to write it in this this formula.

(This may not be the right approach, but any guidance would be helpful!)

• "I have looked up formulas and tried different methods" is not specific enough. Please share exact formulas and methods you have already tried and what those results were. This is a starting point. Also, it appears that you are building an app for which you intend to charge customers if and when it ever works. Stack and other similar forums are not places where people can get customized, time-intensive development services for free. Rather, forum volunteers merely give people a nudge in the right direction if they get stuck on a specific small point in what they themselves have already done. Commented Oct 23, 2021 at 19:19
• HI. Have you considered using a PIVOT Table to get these reports? Commented Oct 27, 2021 at 0:02
• Read query all dates within the date range (a webapps question) for a concise example of including dates in a query. Commented Oct 27, 2021 at 0:43

Try this query:

`=query(A1:E7,"select A, B, avg(D),avg(E) where C >= date '"&TEXT(H1,"yyyy-MM-dd")&"' and C <= date '"&TEXT(H2,"yyyy-MM-dd")&"' group by B,A")`

• As you can see, the "average" function used in the query is different to the one used in a "normal" spreadsheet. Read the query documentation on Aggregation functions for more information.

A "conventional `Pivot Table` with a filter a date range will also generate identical information.

Sample Data

Query Output

PIVOT TABLE

Pivot Date Filter