1

I need to work out some statistics to determine the average time students take to complete a test. I can do the conversion from minutes and seconds to just minutes for each student which is easy however I need to then add the total time taken by all students divided by the number of students and then (this is the issue) convert that average time back to hours minutes and seconds. I can achieve this by using multiple columns but it would be much more efficient if I could just use a single formula that would allow input of time taken to be entered into just one cell.

I am currently able to take a number of times in the format 19m 12s and convert to seconds then calculate the average however it leaves me with a format of 6.78333333 which I now need to convert back to the original input format.

Improve this question
2
  • 1
    Need more data. What units is 6.783333 in? Hours? Days? Minutes? Excel can use times natively, so details of exactly what you're doing would be helpful.
    – Andrew Leach
    Commented Feb 10, 2015 at 11:40
  • the output is in minutes and I require to change this into the original time format
    – Scot
    Commented Feb 10, 2015 at 11:44

1 Answer 1

Reset to default
2

If you are using Excel's native time-handling, then simply use the AVERAGE function.

00:12:19    
00:04:34    
01:34:00    
00:10:23    
00:08:09    =AVERAGE(A1:A5)    00:25:53

If you are not using this, then convert your durations into days (divide the number of seconds by 86400), calculate the average and then format the result cell with the Custom format mm"m" ss"s".

0.008553241     
0.003171296     
0.065277778     
0.007210648     
0.005659722  0.017974537       25m 53s
Improve this answer
1
  • 1
    thanks, the conversion to days was the catalyst I was looking for, its working perfectly
    – Scot
    Commented Feb 10, 2015 at 12:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .