Does anyone know how to create a pace calculator (min/mile) in Exc

[RuSLMaN]

Whenever I try to calculate a running pace (minutes/per mile) it returns a
number in decimal form. But the decimal then has to be converted to seconds.


For example, I want to calculate my pace (minutes/mile) if I ran 4 miles in
31 minutes and 12 seconds. To get an accurate pace, I have to convert 31
minutes and 12 seconds to a decimal format which would be equal to 31.2
minutes. (12 seconds divided by 60 seconds = .2)


Then when I divide 31.2 minutes by 4.0 miles the answer I get in Excel is
7.80.

Since there are only 60 seconds in a minute, 7.80 is actually 7 minutes and
48 seconds per mile. (0.8 x 60 seconds) Is there a function in Excel that
can convert decimals to minutes and seconds without having to do some
advanced programming?

Any ideas?

Thanks,
RuSL

 

[David Biddulph]

If A2 is your number of minutes as a decimal number, then use =A2/(24*60)
and format as time (such as [m]:ss). [This works because Excel dates and
times are in days, so convert from minutes to days.]

You didn't need to do the conversion the long way round converting to
decimals and back again. You could put in the 31:12 as either 0:31:12 or as
31:12.0 (but not as 31:12 because that would be treated as hh:mm, not as
mm:ss), and then just divide by 4.

 

[Bill Sharpe]

Whenever I try to calculate a running pace (minutes/per mile) it returns a
number in decimal form. But the decimal then has to be converted to seconds.


For example, I want to calculate my pace (minutes/mile) if I ran 4 miles in
31 minutes and 12 seconds. To get an accurate pace, I have to convert 31
minutes and 12 seconds to a decimal format which would be equal to 31.2
minutes. (12 seconds divided by 60 seconds = .2)


Then when I divide 31.2 minutes by 4.0 miles the answer I get in Excel is
7.80.

Since there are only 60 seconds in a minute, 7.80 is actually 7 minutes and
48 seconds per mile. (0.8 x 60 seconds) Is there a function in Excel that
can convert decimals to minutes and seconds without having to do some
advanced programming?

Any ideas?

Thanks,
RuSL

Not quite programming, but a bit of function/formula work.

If 7.8 is in A1 then place this formula in another cell:
=INT(A1)+(A1-INT(A1))*60/100.
First part gives you 7; second part gives you 0.8, and the
multiplication/division gives you .48. You could shorten 60/100 to 0.6.
You may also want to format the cell to display only two decimal places.

Bill

 

[David Biddulph]

But why would he want to display as 7.48 (which it isn't) in place of 7:48
(which it is)?

 

[Bill Sharpe]

But why would he want to display as 7.48 (which it isn't) in place of 7:48
(which it is)?
--

You could modify/complicate the formula to change it to text and replace
the period with a colon. However, I think that 7.48 is a big improvement
over 7.8 and understandable to the original poster. He could also split
the formula into two separate formulas and place the minutes in one
column and the seconds in the next column and put a heading at the top
of the two columns. If you really wanted to, you could use three columns
and put the colon in the middle one. <vbg>

Bill

 

[David Biddulph]

But why would you want to go through that sort of nausea to put a colon in a
text string, when Excel time format will display 7:48 as 7:48 anyway? Why
not use it the way it's intended?

 

[Bill Sharpe]

But why would you want to go through that sort of nausea to put a colon in a
text string, when Excel time format will display 7:48 as 7:48 anyway? Why
not use it the way it's intended?

You're right, and a much easier approach including the input you suggested.

Bill

 

[RuSLMaN]

Thanks to both of you (Bill and Dave). Both ways seem to work. I had one
more question for you, if you are inputing your time, would you recommend
splitting it into 2 columns (one for minutes and one for seconds) or is there
a way to put the time in one column and include both minutes and seconds and
then use that for the pace calculation?

For example:
One column Vs Two Columns

|Time (mm:ss)| |Minutes|Seconds|
| 31:12 | | 31 | 12 |

Thanks again for any help!

Russ

 

[David Biddulph]

I prefer the 31:12 so that the processing can be done more simply, but
remember that it would need to be entered as 0:31:12 or 31:12.0 to avoid it
being interpreted as 31:12:00.

 

[William Dalton]

I am trying to apply what you have for an answer but am failing miserably! My ultimate goal is to be able to provide 1/4, 1/2, 3/4 and Lap pace times around an unconventional track. To be able to enter my desired completion time and the desired split times fill in. There would be 15 quarters, not quite a complete 4th lap.

 

[Bernie Deitrick]

William,

In cell B1, enter the track size, in decimal miles.
In cell B2, enter the distance that you want to run.
In cell B3, enter the pace in 0:minutes:seconds that you want to run: 0:8:30 to enter 8 minutes and
30 seconds per mile.

For just lap times, in cell B4, enter this
=IF(ROW(B1)<=$B$2/$B$1,"Lap " & ROW(B1) & ": " & TEXT($B$1*ROW(B1)*$B$3,"mm:ss"),"")

Format B3 and B4 for m:ss, and then copy B4 down the row as far as you want.

Or, put this in A4:
=IF(ROW(B1)<=$B$2/$B$1,"Lap " & ROW(B1) & ": ","")
And this in B4
=IF(ROW(A1)<=$B$2/$B$1,$B$1*ROW(A1)*$B$3,"")

to split the times out so that you can do other math on them.

If you want the times at the 1/4, 1/2 and 3/4 marks around the laps, then use this in A4 (note that
the formula will probably wrap, so take out any line returns):

=IF((ROW(A1)-1 + COLUMN(A1)/4)<=$B$2/$B$1,"Lap " & ROW(A1) &": " & COLUMN(A1) & "/4: " &
TEXT($B$1*$B$3*(4*(ROW(A1)-1)+COLUMN(A1))/4,"mm:ss"),"")

and copy to B44, then copy A44 down for as many rows as you need.

HTH,
Bernie
MS Excel MVP