I need an Excel formula to calculate total splits for a relay race.?

Does anyone know a way in Excel to calculate how a relays best times will calculate out? I'm looking to take mm:ss.00 for each person and total it to a total time. For example: 24.55 + 28.25 + 27.62 + 26.93= 1:47.35. Currently I have the cells set at custom for mm:ss.00 as text will not calculate and numbers mess things up. Time does not work either.|||---EDIT---





Are you asking that you want to sum times like 27.62 and 1:08.55?





A1 = 24.55


A2 = 1:08.55


A3 = 27.62


A4 = 1:02.12





Put this in B1 and copy it down column B to B4


=IF(A1%26gt;1, A1/86400, A1)


...and format column B as [m].ss.00





Then to sum them all...


=SUM(B1:B4)





Alternatively, If you enter in your seconds-only times like this 0:24.55, you could just sum all the cells.





Or if you enter in your times that are over a minute like this


68.55 and use the formula below to sum the cells.








---Original answer---





A1 = 24.55


A2 = 28.25


A3 = 27.62


A4 = 26.93





=SUM(A1:A4)/24/60/60


or


=SUM(A1:A4)/86400





Format the cell with the SUM formula as a custom format...


[m]:ss.00





Dividing by 86400 converts the Total seconds into serial time (a decimal number that represents the portion of one day or 86,400 seconds). Then a custom "time" cell format can display the serial time in the format you define e.g. [m]:ss.00





You were close with using the TIME function but it rounds decimal seconds to an integer so it would drop the .35 seconds in your total.