tracking mile times with excel

[ront02769]

Hi all. I am having to put together a spreadsheet for an organization that administers fitness tests to some 300 individuals. one of the components of the test is the mile run, commonly recorded as 9:16 (nine minutes, 16 seconds).

I am needing a way to enter a 'teams' worth of these (perhaps 20 scores) and get an average overall, then plot that against every persons individual time.

is there an excel function that will allow me to do this, or do I have to do something like two columns, one for minutes and one for seconds, multiply minutes by seconds, total all, divid by the number of people and then divide by 60, toss the remaining seconds over into the seconds group, and do the same type of thing??

thanks!!!

ront

 

[dan1967]

As long as you enter their time in the format "h:m:s", excel will average these values correctly. I just tested with Excel 2k and the average function ("=AVERAGE()&quot worked.

name time average
dan 0:09:16 0:10:09
tiff 0:09:30 0:10:09
jess 0:10:40 0:10:09
becca 0:10:44 0:10:09
tommy 0:10:34 0:10:09

I then created a Line-Column graph (on the Custom Types tab) and the Average column was set as a straight line and each individual time was a column bar against it.

Hope this helps.