# G Sheet How to get average duration time?

I have a serie of time duration which are a result of a formula in a column formatted as Duration as well

Duration :
80:08:00
53:05:00
50:20:00
86:21:00
59:56:00
44:27:00
#VALUE!
35:44:00
39:29:00
133:45:00
35:07:00
32:20:00
.../...

I would like to get the MIN, MAX, and AVERAGE Value. For the Average I tried this formula

=ARRAYFORMULA(AVERAGE(TIMEVALUE(E2:E305)))

I've got this error :

#VALUE! ERROR TIMEVALUE parameter '' cannot be parsed to date/time

How exclude the cell without any values inside ?

• Try FILTER or QUERY functions. Mar 26, 2020 at 15:37

Average
=query({arrayformula(N(\$A\$2:\$A\$13))}; "select avg(Col1) where Col1<>0 label avg(Col1) '' ")

Minimum
=min(query(arrayformula(N(\$A\$2:\$A\$13));"select Col1 where Col1<>0 label Col1 'min'"))

Maximum
=MAX(query(arrayformula(N(\$A\$2:\$A\$13));"select Col1 where Col1<>0 label Col1 'min'"))

Things to note:

• In each case, the values in the "Duration" column must be converted to numeric. This is done by using the N() function.
• The Max and Min formula are made outside the query.
• The where clause includes the condition: Col1<>0
• You gave me the idea of this working formula =AVERAGE(N(\$A\$2:\$A\$13)) Mar 30, 2020 at 17:29
• On the Average formula, the not equal operator is wrong: Col1!=0 should be Col1<>0, by the other hand your spreadsheet might require to use semicolon ; as argument separator instead of comma ,. Mar 30, 2020 at 17:38

EDIT

You mention:

You gave me the idea of this working formula =AVERAGE(N(\$A\$2:\$A\$13))

Be careful. The above formula will give you wrong results so you should not use it

## The correct formulas

Instead you should use the formulas by Tedinoz OR these simpler ones to find the

average value

=AVERAGE(QUERY(A2:A))

min value

=MIN(QUERY(A2:A))

max value

=MAX(QUERY(A2:A))