# 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

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)
{"ft_lang_label":"__label__en","ft_lang_prob":0.8122191,"math_prob":0.972485,"size":503,"snap":"2020-34-2020-40","text_gpt3_token_len":164,"char_repetition_ratio":0.10420842,"word_repetition_ratio":0.0,"special_character_ratio":0.39960238,"punctuation_ratio":0.2642857,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.99811286,"pos_list":[0,1,2,3,4],"im_url_duplicate_count":[null,2,null,3,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-09-28T02:34:40Z\",\"WARC-Record-ID\":\"<urn:uuid:806e87d0-0b02-42d5-af69-c750e206d635>\",\"Content-Length\":\"159960\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:acdcc4a0-eb8b-46d0-af17-277e349f37d4>\",\"WARC-Concurrent-To\":\"<urn:uuid:e3bb8a3b-ef47-4825-a670-f57fb7bba5b0>\",\"WARC-IP-Address\":\"151.101.1.69\",\"WARC-Target-URI\":\"https://webapps.stackexchange.com/questions/139822/g-sheet-how-to-get-average-duration-time\",\"WARC-Payload-Digest\":\"sha1:7CCG6NFFEQ4J5U5GRD24WAFHIK6WVXVJ\",\"WARC-Block-Digest\":\"sha1:AA6YTH2DAMY6CPLNRNLS3CTCTBCYVOVU\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-40/CC-MAIN-2020-40_segments_1600401583556.73_warc_CC-MAIN-20200928010415-20200928040415-00353.warc.gz\"}"}