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!

 

[Special-K99]

What are the three values you're AVERAGing?

 

[Jonmo1]

Welcome to the board.

sounds like your values in H3 H4 and H5 are not actually numbers.
The sum works, because the + symbol converts "Numbers Stored As Text" to real numbers.
=SUM(H3:H5) likely doesn't work either, right?

Average (like Sum) doesn't convert "Numbers Stored As Text" to real numbers.

What is in those cells H3 - H5 ? Are they formulas, or are they entered by hand?

 

[Robert Mika]

Both works for me:
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>H</th><th>I</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">0:04:02</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">0:03:03</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0:05:23</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">0:04:09</td><td style="text-align: right;;">0:04:09</td></tr></tbody></table>

Sheet3​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">H6</th><td style="text-align:left">=AVERAGE(H3:H5)</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">I6</th><td style="text-align:left">=SUM(H3+H4+H5)/3</td></tr></tbody></table></td></tr></tbody></table>

 

[T. Valko]

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!

That seems to indicate that your entries are not true Excel time values but are in fact TEXT strings that just look like time values.

Sometimes this will convert text numbers to numeric numbers...

Select the range of cells in question
Goto Data>Text to Columns
Click Finish

If that worked then you might have to reformat the cells.

 

[dataeater]

The values appear in the formula bar as numbers, such as 00:04:25. These are emergency response times that were downloaded from a database to excel.

 

[Marcelo Branco]

Maybe

=AVERAGE(TIMEVALUE(H3:H5))

 

[Jonmo1]

The values appear in the formula bar as numbers, such as 00:04:25. These are emergency response times that were downloaded from a database to excel.

Yep, they are not real times (at least not according to Excel)...
Excel is treating them as Text Strings.

T.Valko suggested one method to convert them to real times...

Another way is to
1. Copy any blank cell
2. Highlight the range of times
3. Right Click - Paste Special - Values - Add

Hope that helps.

 

[dataeater]

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

 

[Jonmo1]

To get the correct result without modifying the original data first...

=AVERAGE(H3:H5+0)

IMPORTANT
This is an array formula that must be entered with CTRL + SHIFT + ENTER
After entering the formula, highlight the cell and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly the formula will be enclosed in {brackets}