my question isn't exactly about programming but I need help with google spreadsheets.
So my problem is visible here:
https://docs.google.com/spreadsheets/d/1OP_ottKUgSWOoU4hNSYz6X3dN67Z4KiALN0spWm9KoI/edit?usp=sharing
I am a teacher and I have a class of students that is being divided into three teams: yellow, red and green. Each member of a team has to perform the same task three times a week (namely run1, run2 and run3). Every week I calculate the average of the performance of each student (300s = best).
What I would like to know is which team is the most efficient. In the < Average > sheet I want to retrieve only the average value of each student without doing manually copy-paste.
I thought I could use the query command but it fails to give me what I need.
0
votes
I see that you have a few simple formulas in the Avg sheet referencing the Average column in the Students sheet. Why doesn't that work for you?
– Alan Wells
It doesn't work for me because I have to do a manual copy-paste of the average of the week from the < Student > sheet to the < average > sheet. I just wanted copy what I need, i.e. only the averages...
– Elizabex
1 Answers
1
votes
Check out this copy of your spreadsheet and take a look at the sheet 'JP' where I entered in cell A3:
=query(Students!A3:AA, "select A, B, C, G, K, O, S, W, AA")
This formula imports only the columns with the averages.
Then in cell K3, I added a second query
=query(C2:I, "select C, sum(D), sum(E), sum(F), sum(G), sum(H), sum(I) where C <>'' group by C ", 1)
that sums the averages per group (color), per week.
See if that helps ?
