Re: Averaging Time (Excel XP)

Here's everything you ever wanted to know about Dates and Time in Excel CPearson.com .
Format the numbers as time. Seconds are entered 0:0:xx. See Chip's site for a macro that will allow alternate entries.

Re: Averaging Time (Excel XP)

In addition to Doug's suggestion and link,
you could have people enter the values into separate columns (hrs, min, sec) and then use a formula to convert the result into the number of DAYS (the unit of excel time) and then format the resulting formula to the time format desired
<pre>=(hrs+(min + sec/60)/60)/24</pre>

Re: Averaging Time (Excel XP)

In the first case, Excel won't recognize ":09" as any unit of time unless preceded by a zero; those entries are treated as text. In the second case I was able to copy them into a spreadsheet and without any editing run the average calculation; if you are getting a #DIV/0 error, then they may have been entered as text.

FWIW, when you do enter them as time, Excel will treat them as hours & minutes, rather than minutes and seconds, but the averages still work.

Re: Averaging Time (Excel XP)

Hey, it worked! The cells with entries in the format, (3:15, 2:45, etc.), worked out fine once I entered the formula correctly and formatted the cell properly.

As far as dealing with the "seconds only" entries, I opted to do away with the colon and simply enter 3 seconds as the number " 3 ". Naturally, there's no problem in using the AVERAGE function with whole numbers. And it kept data entry speeding along, even faster since we don't have to reach for the colon key. I was able to manipulate the cell A10 to show the average number of seconds in the proper format by using the CONCATENATE function...