Marathon pace sheet

[navin48]

Hi,
I need some help in this, below is my query.

I'm looking at creating a sheet by which i can calculate the total time and pace for a marathon based on the time taken (pace) per mile.
To illustrate this, let's say
Mile 1 - 9:00
Mile 2 - 8:45
Mile 3 - 8:40
Mile 4 - 8:30
Mile 5 - 9:30
Mile 6 - 10:00
Mile 7 - 9:15
Mile 8 - 8:50
and so on.

What i'm trying to put across is that if i have different paces for each mile, the formula should be able to sum up the time from all the 27 cells (26 miles and the extra cell for the 0.2 mile), and -> 1. show me that in hh:mm:ss, 2. show me the average pace for the entire marathon.

I tried doing that in excel, but the total hours shows some weird number and the pace too doesn't come out right.

Please help.

Thanks in advance,
Navin

 

[barry houdini]

Hello Navin, welcome to MrExcel

If you put 9:30 in a cell then Excel reads that as 9 hours and 30 minutes. You could still work with that data but it might be better to input as 0:09:30 then Excel will accept that as 9 mins 30 seconds.

You can format the cells with a custom format of m:ss so that it only display 9:30.

Now when you sum those you should get your circa 4 hour total.

Divide the total by 26.2 for the average pace per mile

 

[navin48]

Hello Navin, welcome to MrExcel

If you put 9:30 in a cell then Excel reads that as 9 hours and 30 minutes. You could still work with that data but it might be better to input as 0:09:30 then Excel will accept that as 9 mins 30 seconds.

You can format the cells with a custom format of m:ss so that it only display 9:30.

Now when you sum those you should get your circa 4 hour total.

Divide the total by 26.2 for the average pace per mile

Thanks a lot for your help Barry.

 

[al_b_cnu]

Hi Navin, Welcome to the board!
Maybe you can do something like this:

Excel Workbook
	A	B	C
1	Mile	Pace	Cumulative Time
2	0
3	1	09:00	00:09:00
4	2	08:45	00:17:45
5	3	08:40	00:26:25
6	4	08:30	00:34:55
7	5	09:30	00:44:25
8	6	10:00	00:54:25
9	7	09:15	01:03:40
10	8	08:50	01:12:30
11	9	09:00	01:21:30
12	10	08:45	01:30:15
13	11	08:40	01:38:55
14	12	08:30	01:47:25
15	13	09:30	01:56:55
16	14	10:00	02:06:55
17	15	09:15	02:16:10
18	16	08:50	02:25:00
19	17	09:00	02:34:00
20	18	08:45	02:42:45
21	19	08:40	02:51:25
22	20	08:30	02:59:55
23	21	09:30	03:09:25
24	22	10:00	03:19:25
25	23	09:15	03:28:40
26	24	08:50	03:37:30
27	25	09:00	03:46:30
28	26	08:45	03:55:15
29	26.2	08:40	03:56:59
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C3	=C2+(B3*(A3-A2))

That said, the conventional wisdom is that you should just determine your average pace and stick to that for each mile of the run (not that I have ever managed to do that yet). But that disussion's for another forum (with the initials 'RW') I suppose.

 

[navin48]

Thanks Alan. Yeah i agree, the idea is to even pace your run through out. Coz of which most of the pace charts out there show you pace equally for all miles. But i just wanted to make my own chart, so that i can factor for slow miles in the beginning, target pace for the middle half and quicker miles towards the end - which hopefully will turnout to be my race in my next marathon
I did the cummulative on an additional column already in the chart i made up, thanks though for your suggestion. It always helps!

 

[al_b_cnu]

Hi Navin,

FYI, here's a link to a s/sheet I concocted, but note that the idea of starting off faster and gradually degrading got a resounding 'NO' from them wot know these things: http://www.mrexcel.com/forum/showthread.php?t=500079

 

[navin48]

Thanks. Do u have the file for this. It'll be easy to fetch stuff from the file than typing it out from here. Thanks.