Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you calculate average time? | Excel Discussion (Misc queries) | |||
show elapsed time ? | Excel Discussion (Misc queries) | |||
Calculating a rate for elapsed time? | Excel Discussion (Misc queries) | |||
How to format elapsed time for swimming scores | Charts and Charting in Excel | |||
elapsed time calculation | Excel Discussion (Misc queries) |