Google-sheets – Time Calculations: find the time taken, and take its average

google sheets

I need to do some calculations. There will be about 100-200 of those 2 entries with different times, competitor numbers and names. I need to calculate the time each competitor's time they took. Then I need to calculate the average of ALL the competitors.

I am using Google Forms to put in the entries, would it be better to stop using Google Forms and enter it into the spreadsheet manually?

Best Answer

=ARRAYFORMULA(QUERY({C1:C200,TIMEVALUE(D1:D200)*IF(B1:B200="End Time",1,-1),E1:E200},"Select Col3,sum(Col2) where Col3 is not null group by Col3 format sum(Col2) '[h]:m'"))

Assuming, No competitions were done past mid night, this should work.

Related Solutions

Google-sheets – Find the nth occurrence of variable, look at adjacent cell and organise in an array

To populate group A, you can just use filter(). Let's say you want group A in Col E, then in E2 (or E1) write:

=filter(A1:A, B1:B="A")

And repeat for all the other groups you want to form. Hope that helps ?

Google Sheets – Query to Find Entries Not in Another Column

This can be done with FILTER by MATCH:

=FILTER(C2:C240,ISERROR(MATCH(C2:C240,B2:B240,0)))

Here, MATCH returns an error if the element is not found in column B (the third argument, 0, requires exact match). The command ISERROR returns true in such a case, and this is what the column C is filtered on.

In SQL, this would be implemented using, e.g., select ... where not exists ... The query language available in Sheets is not powerful enough for that.

Best Answer

Related Solutions

Google-sheets – Find the nth occurrence of variable, look at adjacent cell and organise in an array

Google Sheets – Query to Find Entries Not in Another Column

Related Topic