G Sheet How to get average duration time?

Question

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 ?

Answer 1

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

Answer 2

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

---

Original answer

You need neither the Arrayformula nor the TIMEVALUE functions.
You can simply use the following formulas:

=MIN(E2:E305)
=MAX(E2:E305)
=AVERAGE(E2:E305)