I'm new to the query function in Google Sheets. While I've been able to figure out a good bit of it, I'm struggling on something that seems like it should be relatively easy.
I am a baseball coach and use a sensor to measure our player's bat speed (mph) among other things. The sensor creates a report where each swing is labeled with a date & time (Apr 04, 2022 06:12:15 pm). I'm able to find the overall average and max across dates. What I'm struggling to do is find the average of each specific day due to the fact that each swing includes the time.
Here's the Google Sheet: https://docs.google.com/spreadsheets/d/1fMzsi5dq0EYh7MJqYLIQnlp_7KJk1LeE/edit?usp=sharing&ouid=106268317591050688951&rtpof=true&sd=true
Where I'm struggling is in the 'Individual Players' tab. I'd like to use the drop down in cell B2 to choose a player. I'm able to pull all data (cells A-D) and overall average. Where I'm struggling is cell F8. It's not finding the average by day because the dates also include the time. Therefore, it's showing each individual result. The end goal of this section would be to show growth over time as well as averages of each session.
Here's the current formula:
=QUERY(A:D,"select A, avg(C), max(C), avg(D) group by A label A 'Avg By Date', avg(C) 'Avg Bat Speed', max(C) 'Peak Bat Speed', avg(D) 'Avg Attack Angle' format avg(C) '##.0', avg(D) '##.0'")
I tried using =left(A7, LEN(A7)-12). It works on individual cells, but I can't get it to work as part of the query.
I'm very new to this, so any help would be greatly appreciated.
CodePudding user response：
Answer
The following formula should produce the result you desire:
=QUERY(FILTER({ARRAYFORMULA(REGEXEXTRACT(A4:A,"([A-Za-z] \d{1,2}, \d{4})")),C4:D},A4:A<>""),"select Col1, avg(Col2), max(Col2), avg(Col3) group by Col1 label Col1 'Avg By Date', avg(Col2) 'Avg Bat Speed', max(Col2) 'Peak Bat Speed', avg(Col3) 'Avg Attack Angle' format avg(Col2) '##.0', avg(Col3) '##.0'")
Explanation
The second argument of the QUERY is identical to what you already had, save for the fact that I replaced references to column titles (A, C, D) with references to generic column numbers (Col1, Col2, Col3). This is because one a range is passed through FILTER (used here to exclude blank rows from the output), Sheets no longer recognizes the lettered column titles any more.
Rather than using =LEFT(A7, LEN(A7)-12) to extract the date from column A, I've instead used REGEXEXTRACT, a more robust function that can account for issues that occur when a column does not always follow an identical input format (which is the case for you—some times have a leading 0 and others don't).
Note that REGEXEXTRACT must be contained inside ARRAYFORMULA for it to function on a range of cells rather than just an individual cell. This would also be the case if you wanted to use LEFT rather than REGEXEXTRACT.
Functions used: