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.

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

On Jun 25, 12:05*am, Cathy T <Cathy T...@discussions.microsoft.com>
wrote:
> 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 offto
> 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.

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Cathy T" <Cathy (E-Mail Removed)> wrote in message
news:0E7CA6D5-4402-41D6-A605-(E-Mail Removed)...
> 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.
>
>

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.

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.

"Cathy T" wrote:

> 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.
>
>