How do I set cells to sum/average/etc time? e.g. 1 minute 30 secs

[TavernerResearch]

We need to list times in 'minutes' and 'seconds' as recorded on a stopwatch
and then have excel perform simple calculations such as SUM or AVERAGE.

There appears to be no option to format cells for this sort of time without
having to have dates and AM/PM associated with them.

I imagine angles (measured in minutes and seconds) have the same problem...
1 min 30 seconds could be represented as 1'30", yet still there appears to be
no allowance for this?

 

[andy62]

Format the cells as Time using the hours/minutes/seconds format (37:30:55),
and then enter your data as "0:14:35" for 14 min 35 sec. Yes, in the address
window the data will show up as an AM/PM time, but that doesn't matter. You
can then sum or average across all the data.

Are those of us who use this resource for getting help supposed to chime in
with an answer if we think we know it?

 

[JMB]

This seems to work for me, but there may be a better way. You could use a
custom format of mm:ss, however, you would have to enter the data as a
fraction (seconds / 86400 -total seconds in a day).

I would enter the whole minutes in column A, seconds in column B, then in
column C

=(A1*60+B1)/86400

and appy the formatting to column C.