"If you just want to do this quickly, select Time as the option from the drop down box and it should work as expected:
I have tried and cannot replicate your results, I think that you are messing up hours/minutes/seconds, Time fields are usually stored as hh:mm:ss, and then just displayed how you want. I recommend you try just using the built in Time field (as above) then try changing it later to hh:mm / mm:ss / hh:mm:ss, I think what is happening is you are storing as mm:ss, and displaying the average as hh:mm, or similar which is why you are getting weird results."
"I was trying to do some math on time values, and I ended up here at this page looking for help. Strangely, though, none of the cell format suggestions listed here were working for me...
...until I finally realized that my data, which I'd imported from a file, had spaces in front of most of the time values. Once I removed the spaces, Excel was HAPPY to run all manner of formulas, correctly, on my time data.
Something to look for if you're having trouble for what seems like ""no reason."""
"One thing to watch for when setting time formats in excel is that the format ""hh:mm"" ignores the number of days and similarly ""mm:ss"" will ignore the number hours entered in the cell.
If you are getting strange looking results when manually calculating average times try putting square brackets around highest level unit of time in your format:
[hh]:mm [mm]:ss
This will return the absolute number of hours or minutes - useful if your sum of times goes over 24 hours or 60 minutes" "One thing to watch for when setting time formats in excel is that the format ""hh:mm"" ignores the number of days and similarly ""mm:ss"" will ignore the number hours entered in the cell.
If you are getting strange looking results when manually calculating average times try putting square brackets around highest level unit of time in your format:
[hh]:mm [mm]:ss
This will return the absolute number of hours or minutes - useful if your sum of times goes over 24 hours or 60 minutes"
If you want duration time, not time of day, use the format "[h]mm:ss" and not the format "mm:ss". Normal math functions (e.g. average, sum, etc.) should then work.
