Calculate average

[raji40]

Hi all,

I'm trying to calculate average in min:secs but when applying the formula, its giving error. I even tried changing the format but unable to get the results. Please help. Sample is attached.

00:00:00

00:02:46

00:00:00

00:00:00

00:00:00

00:00:00

00:00:00

00:00:00

00:00:00

00:00:00

<colgroup><col></colgroup><tbody>
</tbody>

 

[PlusX EAI]

=AVERAGE(A1:A10)

Result is 00:00:17, which is correct

 

[raji40]

If I  try with a different set of values, then it gives an error.<br><table border="0" cellpadding="0" cellspacing="0" style="width: 64px" width="64">
<colgroup><col width="64" style="width:48pt">
</colgroup><tbody><tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:02:46</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:00:43</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:00:41</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:00:53</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:00:02</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:00:41</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:00:34</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:00:38</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:01:51</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt">00:01:46</td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" align="center" style="height:12.75pt"><em><strong>#DIV/0!</strong></em></td></tr></tbody></table><table border="0" cellpadding="0" cellspacing="0" style="width: 64px" width="64">
<colgroup><col width="64" style="width:48pt">
</colgroup><tbody><tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt"><br></td></tr>
<tr height="17" style="height:12.75pt">
<td height="17" class="xl65" width="64" style="height:12.75pt;width:48pt"><br></td>
</tr>
<tr height="17" style="height:12.75pt">
<td height="17" align="center" style="height:12.75pt"><br></td></tr></tbody></table><br>

 

[Declamatory]

Hi Raji40,

I just tried it with the values you posted and the AVERAGE formula calculated it fine as 00:01:04

 

[PlusX EAI]

Yes, it should work. You must have selected a wrong Range (A1:A10...)

 

[raji40]

What is a "divide by zero" error?

 

[joeu2004]

I'm trying to calculate average in min:secs but when applying the formula, its giving error. I even tried changing the format but unable to get the results.

My guess: the "times" are text, not numeric values. Confirm by entering =ISTEXT(A1) somewhere, where A1 is a cell with one of your "times".

The #DIV/0 error indicates that AVERAGE thinks there are zero values to average. That is because AVERAGE ignores text in a range.

Of course, you should try to determine why the "times" are text. There are many possible reasons.

We cannot give a dispositive answer and remedy unless you upload an example Excel file (redacted) to a file-sharing website and post the public/share URL here.

Also, it would be helpful to know how the "times" were entered. For example, were they copy-and-pasted from a table in a webpage?