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