elapsed time average calculcations
 

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

I'm afraid you would need VBA to do this.
You can have a look at the function Datediff(), maybe you can figure
out a way with cell formulas to get it done but I don't.


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

Any way to hide fields that are just 0's? I have attached the
spreadsheet..
thanks again


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

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

Thanks for the response. However, what you say is for the second part I
am assuming would replace my current average formula. I dont think I
quite understand what the first forumala is for...


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

My fault, looks like it gives the average time in days. Can this be
modified to give the average in minutes?

Thanks soo much!


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

No, it gives the average time in time. Format the cell as time.

--

HTH

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


"relux" wrote in
message ...

My fault, looks like it gives the average time in days. Can this be
modified to give the average in minutes?

Thanks soo much!


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