Problems creating an Average

merrellpa

New Member
Joined
Mar 9, 2009
Messages
7
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.JPG
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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"),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,079
Messages
5,599,636
Members
414,326
Latest member
kfg1287

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

Which adblocker are you using?

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
Top