Time - Calculating "Minutes Per MIle"

Question

Level 1

8 points

Time - Calculating "Minutes Per MIle"

For years, I have using an Excel spreadsheet to input my running times. I have a column for "miles", one for "Time" (hours, minutes, second using a "date" cell format) and a column entitled "Minutes per Mile." The formula for obtaining the Minutes per Mile is simple: Miles/Time or "=D15/D14" for example. But when I import my Excel file into Numbers, it cannot correctly populate the "Minutes per Mile" cell. A warning message states: "the Operator "/" expects a number, but cell D14 contains a date."
How can I make this work in Numbers?

MacBook Pro, Mac OS X (10.4.11)

Posted on Dec 29, 2007 4:47 PM

Reply

Answer 1

Dec 29, 2007 5:18 PM in response to Bill Miecuna

Try this formula:

=D15/(TIMEVALUE(D14)*1440)

Time calculation in Pages is less than straightforward, requiring more conversions of units than Excel does. The syntax of this formula is "convert the time in D14 to a decimal fraction of a 24 hour day, then multiply by 24 hour/day and 60 minutes/hour, i.e., 1440, to get the number of minutes."

BTW, the Minutes column needs to formatted to show minutes (use the Cell Inspector) and must be entered using the colon character to separate hours:minutes:seconds. When you enter one hour and 45 minutes [1:45] in Pages, it reads it as "Today at 1:45 a.m."

Good luck,

Terry

Message was edited by: Terry Keelan1

Reply

Answer 2

Bill Miecuna Author

Level 1

8 points

Dec 30, 2007 10:09 AM in response to Terry Keelan1

Thank you, Terry but it still doesn't work. I had to modify the formula you gave me as follows:
=C15/(TIMEVALUE(D15)*1440)
C15 is the actual number of miles, for example 13.4 (this cell is formatted as a number); D15 is total time of the run (hopefully you refer to this as the Minutes column), 2:37:57 AM (this cell is formated as Time using the cell inspector as you suggested); and E15 is the minutes per mile (this is also formatted as Time). When I insert the formula into cell E15, I receive an answer of 0.09. I don't know what that means; the time should have been 11:47 meaning 11 minutes and 47 seconds per mile. Am I formatting the cells incorrectly or is it the formula? I tried opening the Excel file in a program called NeoOffice and the calculation worked just fine. This is driving me crazy. Can you give me some more assistance? Finally, you are talking about "Numbers" not "Pages", right!

Reply

Answer 3

CDS1

Level 2

215 points

Dec 30, 2007 12:24 PM in response to Bill Miecuna

I think the formula is inverted- giving miles per minute. If I read the post correctly Column C has the miles. Using the formula here:

=(TIMEVALUE(D15)*1440)/C15

is 11.78- which is a decimal equivalent of 11 minutes 47 seconds.

Craig

Reply

Answer 4

KOENIG Yvan

Level 8

41,845 points

Dec 30, 2007 12:34 PM in response to Bill Miecuna

Hello

Don't worry, Terry was just fooled by typos embedded in your original message.

User uploaded file

C15 13.4
D15 02:37:57

E15 =TIME(0,0,ROUND(TIMEVALUE(D)*86400/C,0))

gives the standard: 00:11:47

Yvan KOENIG (from FRANCE dimanche 30 décembre 2007 21:30:19)

Reply

Answer 5

Bill Miecuna Author

Level 1

8 points

Dec 30, 2007 4:13 PM in response to KOENIG Yvan

Hello Yvan,

Thank you very much. IT WORKS! I can't tell you have much I appreciate your assistance as well as Craig's. Have a great New Year!

Reply

Answer 6

Jan 2, 2008 11:50 AM in response to Bill Miecuna

Am I correct in thinking that you are a runner, and this is the formula for computing your pace for a run? If not, it is good for that too, and just what I was looking for.

Thanks to everyone who contributed to this thread.

Reply

Answer 7

Bill Miecuna Author

Level 1

8 points

Jan 2, 2008 1:08 PM in response to Michelle_eris

Yes, I am a runner off and on. I used this format to train for the Long Island and NYC Marathons in 2007. If you'd like, I can emailed you the file so that you can see how it's set up. I don't know if this Discussion Board allows for attachments and I can't seem to be able to paste a screenshot on this board. My emails: billy_miecuna@mac.com

Reply

Answer 8

WWJD

Level 6

15,830 points

Jan 2, 2008 1:31 PM in response to Bill Miecuna

Send Feedback to Apple asking for improved date and time handling in Numbers.

Regards,

Reply