To take hh:mm:ss format and change it to seconds for averaging...then back
Posted by Chris Rock on August 15, 2001 7:42 AM
I have a 31 day list with 2 columns: CALLS and DURATION.
When a given day has a CALL, it also has a DURATION. However, with 0 CALLS, there is a DURATION of 0.
The DURATION is in hh:mm:ss format - it gets input from another system. On another part of the sheet, I'd like to take that hh:mm:ss number, break it down into total seconds, and then, ignoring the 0 DURATIONS, I'd like to get the average of all the DURATIONS in seconds, then put that back into a hh:mm:ss format. The biggest problem I have is that the hh:mm:ss value shows as the time-date serial number. This is useless to me when I try to run calculations on it.
Any way to do this? I can use Data / Text to columns to break the hh:mm:ss format into hh | mm | ss - but I am having problem with the averaging, due to the whole 60 seconds in a minute thing.
I'm open to anything, formulas, macros, even chanting magic words.
Thanks.
I can provide a more specific example if needed.
Posted by Mark W. on August 15, 2001 7:56 AM
Chris, perhaps I don't quite understand what you're
trying to accomplish, but... consider this.
If cells A1:A4 contain the following times:
05:23:33
04:03:12
0
02:12:03
Then, the array formula...
{=AVERAGE(IF(A1:A4,A1:A4*86400))}
...will compute the average (excluding 0's) of
those times in seconds (13,976 seconds).
[Here's where I get confused ] You request that
these seconds be converted back to hh:mm:ss format!
To do this you'd need to convert the seconds back
into an internal time value using =13976/86400.
I not quite sure where that gets you because
that's the same value you'd get using the array
formula, {=AVERAGE(IF(A1:A4,A1:A4))}, in the 1st
place!
Posted by Chris Rock on August 15, 2001 9:03 AM
Mark,
I can see how you're confused. I like the Array formula, and with your example, it looks like this will work.
My biggest confusion is Excel's conversion from time format to serial number. Perhaps I don't need to worry about it at all, like your example seems to show.
I will apply the example you've provided to my data and see if it works. Thanks for your help.
The TRICKIEST part of this whole matter is that I am working toward a solution that I already have...I am trying to re-create a solution that has already been calculated. It's hard to explain, but it would make my job alot easier if my spreadsheet could calculate this average.
