elapsed time average calculcations

[relux]

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!

[Dnereb]

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.

[relux]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[relux]

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]

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

Thanks soo much!

UPDATE: adding *24*60 seems to do the trick. thanks guys.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>