Formula for 'Numbers' needed - recording my running times

[madjockmcferson]

Hi there all, I've tried doing this but I am far away from being an expert with spreadsheets.

I am starting to run and I want to record my times to see if I am improving. Its a very simple spreadsheet.

Distance (in miles)
Time (in minutes)
Time in minutes per mile

My problem is that say I did 2.6 miles in 20 minutes and 30.03 seconds and i enter this in the formula as 20.3003 / 2.6 it doesn't give a correct answer as time is based on 60 not 100. 20.3003 doesn't translate directly into a decimal as 20.3003....

In other words do I need to convert 20 mins and 30.03 seconds into decimal ie....something like 20.5 (30 seconds being 0.5 of a minute) or is there a built in formula that I can use....?!?

My brain hurts....as well as my legs!

 

[iMacmatician]

Hi there all, I've tried doing this but I am far away from being an expert with spreadsheets.

I am starting to run and I want to record my times to see if I am improving. Its a very simple spreadsheet.

Distance (in miles)
Time (in minutes)
Time in minutes per mile

My problem is that say I did 2.6 miles in 20 minutes and 30.03 seconds and i enter this in the formula as 20.3003 / 2.6 it doesn't give a correct answer as time is based on 60 not 100. 20.3003 doesn't translate directly into a decimal as 20.3003....

In other words do I need to convert 20 mins and 30.03 seconds into decimal ie....something like 20.5 (30 seconds being 0.5 of a minute) or is there a built in formula that I can use....?!?

My brain hurts....as well as my legs!

This is how I did it.

Use "=FLOOR(B2,1)+(B2-FLOOR(B2,1))*100/60" where B2 is the cell location I used. This gives me "20.5005" (20.5 minutes) as the output for "20.3003" (20 mins 30.03 secs).

 

[adrianblaine]

This would be my suggestion...

the "total time" cell is simply converting the minutes into seconds, adding all the seconds together, and converting it back to minutes. the min/mile cell than dived the minutes by the miles. Of course the minutes/mile cell is not in minutes and seconds though. if you want that you'll need to covert the .8848 back into seconds (53.08)

 

[madjockmcferson]

Cheers guys, I started having High School Maths lessons flashbacks....ugh! thanks!