Problems creating an Average

[merrellpa]

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

 

[Colin Legg]

How about this for the average?

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

Gives a result of 00:25:59

With the cell containing the formula formatted as required.

 

[merrellpa]

See i enter that but i get:

#DIV/0!

 

[Colin Legg]

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:

=D2-C2

Copied down...


Then you could change the average formula to this:

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

 

[merrellpa]

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