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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Colin Legg

MrExcel MVP
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
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"),"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,706
Messages
5,833,238
Members
430,198
Latest member
KitaYama

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