up vote 1 down vote favorite
share [g+] share [fb]

I have these values

01:15
05:00
01:31
02:00
02:21
02:39
03:29
08:00

I highlighted all these cells and went to format cells -> custom -> and choose mm:ss

I then tried to use the built in average function in Excel 2007 

=AVERAGE(D31:D38)

The result is 0.0

Of course this is not the number result that it should be(I have not calculated it manually yet but I am sure it is not 0).

I am not sure if it has the fact to do when you click on the cell it has something like this

"12:08:00 AM"

I am not sure if that is what is screwing it up.

link|improve this question
72% accept rate
feedback

3 Answers

up vote 2 down vote accepted

If you just want to do this quickly, select Time as the option from the drop down box and it should work as expected:

alt text

I have tried and cannot replicate your results, I think that you are messing up hours/minutes/seconds, Time fields are usually stored as hh:mm:ss, and then just displayed how you want. I recommend you try just using the built in Time field (as above) then try changing it later to hh:mm / mm:ss / hh:mm:ss, I think what is happening is you are storing as mm:ss, and displaying the average as hh:mm, or similar which is why you are getting weird results.

link|improve this answer
I think I know what happened. I had the fields as Time but I had average as decimal. When I changed it to time it worked. I then was able to use format cells and bring it down to mm:ss. – chobo2 Jan 30 '10 at 21:07
A very quick and efficient method, +1. – John T Jan 30 '10 at 23:12
feedback
up vote 1 down vote

To get the times formatted as "mm:ss" I had to enter them as follows:

00:01:15
00:05:00
00:01:31
00:02:00
00:02:21
00:02:39
00:03:29
00:08:00

i.e. zero hours, some minutes and some seconds.

Changing the format to Time displays the "00:" for the hours.

Then when I average them I get 03:17

link|improve this answer
Hmm, I got 3:16 as you can see below. Weird... – Connor W Jan 30 '10 at 20:46
@Connor W - I retyped my values so they were really in "mm:ss" format rather than "hh:mm" format as Excel assumed they were when I pasted them into the sheet. It could be just rounding error in the function when dealing with "hh:mm" rather than "mm:ss" – ChrisF Jan 30 '10 at 20:49
I get 3:16:53, this is rounded up. – William Hilsum Jan 30 '10 at 20:49
@Wil - yes - as I was working in "mm:ss". With "hh:mm" the time will be truncated. – ChrisF Jan 30 '10 at 20:51
feedback
up vote 1 down vote

I just inputted the values you gave to Excel, set it to give an average and it gave back 3:16. You can see from the pics the formula for the average and the value of each cell. I set all the cells to "Time".

alt textalt text

Hope that helps

link|improve this answer
What did you choose for Time though. There are lots of options? – chobo2 Jan 30 '10 at 20:49
Your images are broken, are you able to reupload them? – Gareth Sep 13 '11 at 2:53
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.