Stephen Kairys
asked on
Excel 2013 - Calculating elapsed time with formula
I am designing a spreadsheet to calculate elapsed time in a race, based on a particular pace per mile.
e.g. at a 12:00 pace, mile 1 would be 12:00, mile 2, 24:00, mile 3, 36:00 etc.
I thought I had established the proper time format for the column... as the setting I selected seems to support more than 24 hours (37:30:55).
However, when I do the math per a formula:
=<MILES >* 0:12:00
where MILES is the value in the row containing the mile# (1, 2, 3, etc.)
it stops working at Mile 3.
0:12:00 * 1 = 0:12:00
0:12:00 * 2 = 0:24:00
0:12:00 * 3 = 1:12:00 (s/b 0:36:00)
What am I doing wrong/missing?
Thanks,
Steve
e.g. at a 12:00 pace, mile 1 would be 12:00, mile 2, 24:00, mile 3, 36:00 etc.
I thought I had established the proper time format for the column... as the setting I selected seems to support more than 24 hours (37:30:55).
However, when I do the math per a formula:
=<MILES >* 0:12:00
where MILES is the value in the row containing the mile# (1, 2, 3, etc.)
it stops working at Mile 3.
0:12:00 * 1 = 0:12:00
0:12:00 * 2 = 0:24:00
0:12:00 * 3 = 1:12:00 (s/b 0:36:00)
What am I doing wrong/missing?
Thanks,
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! That works, now. Have a good evening!
Steve
Steve
ASKER
Cell C2 contains the formula C1 (the pace per mile) * A2 (the mile marker in that row)
I wanted C3 to be C1 * A3 but it came out to be C2 * A3. (12:00 * 2). In this case, by blind luck 24:00 was OK.
I wanted C4 to be C1 * A4 but it came out to be C3 * A4 (24:00 * 3) which is, of course 1:12:00.
So the question is now: how can I "trick" Excel into copying the formula C1 * A2 so that C1 is held constant?
Thanks...your remark about Mile 6 led me down this path! :)