# Problems creating an Average

#### merrellpa

##### New Member
Here is a Picture of my Spreadsheet. What i want to do is Have a call time average. I am running into the problem where my calculation for the Call time is making the result as 0:00:00 if there is no start or end time. So when i try to get an average it will Include all 25 fields.

1) Either have it so that The call time is blank when there is no start and end time.

2) Or the Call time Average does not include all the Zeros

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How about this for the average?

Code:
``=SUM(E2:E25)/COUNTIF(E2:E25,">0")``

Gives a result of 00:25:59

With the cell containing the formula formatted as required.

See i enter that but i get:

#DIV/0!

Hi,

What formulas do you have in column E?

I'm suspecting they are producing text types rather than numeric types...

It should be in E2:
Code:
``=D2-C2``

Copied down...

Then you could change the average formula to this:
Code:
``=IF(SUM(E2:E25),SUM(E2:E25)/COUNTIF(E2:E25,">0"),"")``

you are right... that works... Thx much

Replies
3
Views
154
Replies
5
Views
179
Replies
2
Views
162
Replies
0
Views
196
Replies
12
Views
348

Threads
1,219,569
Messages
6,149,039
Members
450,852
Latest member
dmotz

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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