1

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.

Sample Data

  • 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.

enter image description here

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'" )

enter image description here

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

Improve this question
3
  • 4
    "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.
    – Erik Tyler
    Oct 23, 2021 at 19:19
  • HI. Have you considered using a PIVOT Table to get these reports?
    – Tedinoz
    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.
    – Tedinoz
    Oct 27, 2021 at 0:43

1 Answer 1

Reset to default
0

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.

  • In addition, the query applies only to a given date range. More information

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

Sample Data data

Query Output

query

PIVOT TABLE

Pivot

Pivot Date Filter

datefilter

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.