# Calculate average

#### raji40

##### New Member
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>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### PlusX EAI

##### New Member
=AVERAGE(A1:A10)

Result is 00:00:17, which is correct

#### raji40

##### New Member
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

##### Active Member
Hi Raji40,

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

#### PlusX EAI

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

#### raji40

##### New Member
What is a "divide by zero" error?

#### joeu2004

##### Banned user
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?

Replies
1
Views
150
Replies
1
Views
306
Replies
6
Views
1K
Replies
4
Views
2K
Replies
1
Views
165

1,191,007
Messages
5,984,129
Members
439,872
Latest member
ExcelRM

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back