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

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! :)