calculate average given minutes and seconds

[Cathy T]

I'm trying to calculate the average minutes and seconds for a given task. We
have 4 sets of minutes and seconds. I want the minutes and seconds to
display as mm:ss but when I select this as a format it seems to round off to
nearest minute. I want the resulting average to display as mm:ss also.

Can someone please tell me the correct format to use in Excel 2007 to get
mm:ss and also the formula to calculate the average.

 

[Pete_UK]

Assuming your times are in A1:A4, put this in A5:

=AVERAGE(A1:A4)

If the first 4 cells are formatted as [mm]:ss, I would expect A5 to
take the same format when you enter the formula, but if not then
choose Custom in the Format cells dialogue box and set it to [mm]:ss.

You should note that if your time is 4 minutes and 30 seconds, for
example, and you enter 4:30, then Excel will take this as meaning 4
hours and 30 minutes, even though it will display as 4:30. You need to
enter the data as 0:4:30 or as 4:30.0 - this might be what caused you
the problem.

Hope this helps.

Pete

 

[Bernard Liengme]

Cathy,
It always helps to show what you did and what the result is.
This is what I did
In A1:A3 I entered some times (mins:sec)
0:02:23
0:04:45
0:06:45

0:04:38


In A5 I used =AVERAGE(A1:A3)
I can format everything with custom format mm:ss so as to not display hours

How does this differ from what you did?
best wishes

 

[Cathy T]

Thanks for the replies. I guess while trying to be concise and not overload
with too much information I did not give enough. Sorry.

I was able to get it to work using the hours yesterday before I posted. I
am not that familiar with Excel 97 and when I tried to format using the mm:ss
(I also did not key a 0 as the hour when I used this format) I got the
rounding errors I described earlier. I will try keying the hours and using
the mm:ss format and see if that works.

 

[Cathy T]

THANKS! I used the spreadhseet where I had entered with the hours hh:mm:ss
and then just reformatted to the mm:ss and no more rounding error.