# 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 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))` – dubis Mar 30 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 at 17:38

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)`````` • That's working because you carried out #VALUE! , in my case more than 300 lignes and I would like all the cells with #VALUE! or ERROR are not taken in account. – dubis Mar 31 at 6:30
• "That's working because you carried out #VALUE!". Correct. I followed your post saying "How exclude the cell without any values inside ?" In any case the answer by Tedinoz is what you actually need. Be careful though. You mention "You gave me the idea of this working formula `=AVERAGE(N(\$A\$2:\$A\$13))`". This formula will give you wrong results so you should not use it. – marikamitsos Mar 31 at 12:07