Averaging time error

[dataeater]

I attempted to use the AVERAGE function on a column of response times in the format 00:04:22. I get the #DIV/0! error which says I'm dividing by zero or empty cells, yet that is not the case. I have reformatted the cells using various formats 37:50:22, [h]:mm:ss, hh:mm:ss, etc. Nothing seemed to work.

If I manually input the formula =sum(H3+H4+H5)/3 I get the correct average. But when I try to use the auto function AVERAGE on the same three cells, I get errors or zeros.

I have over 2000 seperate numbers in the column, so manually inputing the formula is out of the question. Your help is greatly appreciated. Thanks!

 

[Marcelo Branco]

Marcelo, I tried =AVERAGE(timevalue(H3:H5)) and received the error #####.

It worked for me being H3:H5 text as
37:44:22
00:44:23
00:43:22



M.

 

[dataeater]

jonmo1 and T. Valko, many thanks.

I converted the numbers to times per your instruction and formatted the cells to [h]:mm:ss and it appears to work.

Again, thank you

 

[T. Valko]

jonmo1 and T. Valko, many thanks.

I converted the numbers to times per your instruction and formatted the cells to [h]:mm:ss and it appears to work.

Again, thank you

Good deal. Thanks for the feedback!