Last week at Orangetheory (the best coach-led high intensity interval training, check it out), I ran a benchmark 1 mile. Did a personal record of 8:05, BTW. So what average speed must I run to complete 1 mile in shorter or longer times? For that, we use the Time function.
The syntax is:
=TIME(hours, minutes, seconds)
The function returns a time we can format as hh:mm:ss. For example, if you want to display 3 hours, 15 minutes and 30 seconds, you would have =TIME(3, 15, 30).
Starting in A2, I use AutoFill to create a column of speeds in .1 MPH increments, from 4.0 through 14 in case Usain Bolt wants to use the chart.
Down column B, I insert the Time function.
I don’t care about hours, and the calculation is easier to lump minutes and seconds together. Keeping in mind there are 3600 seconds in an hour, I enter in B2:
=TIME(,,3600/A2)
With 4 in A2, this returns a result of 12:15 AM. So I press Ctrl + 1 (Cmd + 1 on the Mac) for the Format Cells dialog. Under the Number tab, I choose Custom at the bottom of the left-hand list and delete what’s in the Type box. I then enter the format m:ss. That way I won’t have leading zeros for single-digit minutes.
This tells me that at 4 MPH, it will take 15:00 to complete 1 mile. Now AutoFill down to the bottom.
You can download my completed Excel sheet. To make it fit on one sheet of paper, I split the column pairs into three sections.