Average time durations in google form populated google sheet

Question

I have a google sheet that is auto populated from a google form. There is a column with time durations:

When I try to average these durations I get the divide by zero error. I tried changing the cell format, but nothing works. And in fact, it wont change at all no matter what format I choose. I think the format is locked based on the form that is populating the sheet.

Any help on getting the average in a cell below the column?

Answer 1

Please try a formula of this kind:

=sum(ArrayFormula(1*(A1:A15)))/counta(A1:A15)

with formatting to suit.

Answer 2

The problem is you are getting values as text.

=ArrayFormula(AVERAGEIF(A1:A18*1,">0"))

• *1 converts text into a number
• condition ">0" to skip empty cells, converted to 0.
• format the result as duration.