CODEWITHSUNDEEP
google-sheetsdurationformulas
MSBSresearch
MSBSresearch

Reputation: 53

Average time durations in google form populated google sheet

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

enter image description here

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?

Upvotes: 4

Views: 3592

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18717

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.

Upvotes: 2

pnuts
pnuts

Reputation: 59475

Please try a formula of this kind:

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

with formatting to suit.

Upvotes: 2

Related Questions