# 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.
– Rubén
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 `,`.
– Rubén
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))``

You need neither the `Arrayformula` nor the `TIMEVALUE` functions.
``````=MIN(E2:E305)