First part,

I would use

=SUMPRODUCT(--((D3:D20<"")*OR(C3:C20<"")),(D3:D20-C3:C20))/SUMPRODUCT(--((
D3:D20<"")*OR(C3:C20<"")))

Second part

=IF(AND(D3="",C3=""),"",INT(D3-C3)&" days, "&INT(24*MOD(D3-C3,1))&" hours,
and "&ROUND(60*MOD(24*(D3-C3),1),0)&" minutes")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"relux" wrote in
message ...

Hey guys,

Here is my situation. I have a formula in Column F that is:

=INT(D3-C3)&" days, "&INT(24*MOD(D3-C3,1))&" hours, and
"&ROUND(60*MOD(24*(D3-C3),1),0)&" minutes"

It takes the time from column c and d and shows the elapsed time. What
I need to do now is make a field in for example F 30 that shows the
average elapsed time. In other words, average up all the elapsed times
in Column F. I can't figure out a way to do this..

Also, I have the formula above all the way down the spreadsheet. Is
there a way to hide "0 days, 0 hours, and 0 minutes" if there is
nothing in the C and D columns?


I've attached the spreadsheet..


Thanks in advance!


+-------------------------------------------------------------------+
|Filename: emails2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3750 |
+-------------------------------------------------------------------+

--
relux
------------------------------------------------------------------------
relux's Profile:
http://www.excelforum.com/member.php...o&userid=20029
View this thread: http://www.excelforum.com/showthread...hreadid=399494