calculate average given minutes and seconds
 

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.

Answer: calculate average given minutes and seconds
 

To display minutes and seconds in the format mm:ss:

1. Select the cells containing the minutes and seconds data.
2. Right-click and select "Format Cells".
3. In the "Number" tab, select "Custom" from the Category list.
4. In the "Type" field, enter "mm:ss" and click "OK".

To calculate the average of the minutes and seconds data:

1. Select the cell where you want to display the average.
2. Type "
   Formula:

   ---

   =AVERAGE( 


   ---

   " and then select the cells containing the minutes and seconds data.
3. Type "
   Formula:

   ---

   ) 


   ---

   " and press Enter.

This will calculate the average of the minutes and seconds data and display it in the selected cell. To format the result as mm:ss, you can apply the custom number format as described above.

calculate average given minutes and seconds
 

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

calculate average given minutes and seconds
 

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 wrote in message
...
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.

calculate average given minutes and seconds
 

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.

calculate average given minutes and seconds
 

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.