1

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 ?

1
  • 1
    Try FILTER or QUERY functions.
    – Rubén
    Mar 26, 2020 at 15:37

2 Answers 2

2

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
2
  • You gave me the idea of this working formula =AVERAGE(N($A$2:$A$13))
    – dubis
    Mar 30, 2020 at 17:29
  • 1
    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
1

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

enter image description here


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)

max, min, average of time duration

1
  • 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, 2020 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.