If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Running Pace Calculator

Thread starter al_b_cnu
Start date Oct 7, 2010

A

al_b_cnu

Well-known Member

Oct 7, 2010

#1

Hi All,

I recently ran (my first) marathon, but far slower than I hoped for, basically because I started off far too fast.

I have constructed a s/sheet to calculate the pace per mile I should be going in order to achieve an average target pace:

Excel Workbook

A

B

C

D

E

1

Mile

Pace

2

Total Miles:

26.2

1

08:06

3

Variance:

10%

2

08:10

4

Target Pace:

09:00

3

08:15

5

Total Time:

03:55:48

4

08:19

6

5

08:23

7

6

08:27

8

7

08:32

9

8

08:36

10

9

08:40

11

10

08:45

12

11

08:49

13

12

08:53

14

13

08:57

15

14

09:02

16

15

09:06

17

16

09:10

18

17

09:15

19

18

09:19

20

19

09:23

21

20

09:27

22

21

09:32

23

22

09:36

24

23

09:40

25

24

09:45

26

25

09:49

27

26

09:53

28

26.2

09:54

29

30

Total

04:03:43

Sheet1

Excel 2003

Cell Formulas
Range	Formula
B5	=Total_Miles*Target_Pace
E2	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D2-1))/((Total_Miles-1)/2))))
E3	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D3-1))/((Total_Miles-1)/2))))
E4	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D4-1))/((Total_Miles-1)/2))))
E5	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D5-1))/((Total_Miles-1)/2))))
E6	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D6-1))/((Total_Miles-1)/2))))
E7	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D7-1))/((Total_Miles-1)/2))))
E8	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D8-1))/((Total_Miles-1)/2))))
E9	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D9-1))/((Total_Miles-1)/2))))
E10	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D10-1))/((Total_Miles-1)/2))))
E11	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D11-1))/((Total_Miles-1)/2))))
E12	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D12-1))/((Total_Miles-1)/2))))
E13	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D13-1))/((Total_Miles-1)/2))))
E14	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D14-1))/((Total_Miles-1)/2))))
E15	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D15-1))/((Total_Miles-1)/2))))
E16	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D16-1))/((Total_Miles-1)/2))))
E17	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D17-1))/((Total_Miles-1)/2))))
E18	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D18-1))/((Total_Miles-1)/2))))
E19	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D19-1))/((Total_Miles-1)/2))))
E20	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D20-1))/((Total_Miles-1)/2))))
E21	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D21-1))/((Total_Miles-1)/2))))
E22	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D22-1))/((Total_Miles-1)/2))))
E23	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D23-1))/((Total_Miles-1)/2))))
E24	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D24-1))/((Total_Miles-1)/2))))
E25	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D25-1))/((Total_Miles-1)/2))))
E26	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D26-1))/((Total_Miles-1)/2))))
E27	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D27-1))/((Total_Miles-1)/2))))
E28	=Target_Pace-(Target_Pace(Variance((((Total_Miles-1)/2)-(D28-1))/((Total_Miles-1)/2))))
E30	=SUM(E2:E28)

Excel Workbook

Name

Refers To

Target_Pace

=Sheet1!$B$4

Total_Miles

=Sheet1!$B$2

Variance

=Sheet1!$B$3

Workbook Defined Names

I have the following named ranges:
Total_Miles in B2
Variance in B3
Target_Pace in B4
Total_Time in B5

I created what I thought was the perfect formula in column E to start me off at 10% above the target pace, and slowly degrade to -10% by the end. Sadly, the total pace time in E30 differs to the expected value (in B5).

Can you advise please?

Many thanks

Alan

Sort by date Sort by votes

Ruddles

Well-known Member

Oct 7, 2010

#2

Surely you don't take 9:54 to run the final 0.2 miles?

Upvote 0

A

al_b_cnu

Well-known Member

Oct 7, 2010

#3

Hi,

The Pace is minutes per mile. In this example I am aiming to run the entire 26.2 miles at an average of 9 minute miles, but want to vary this, starting at 9:00 minus 10% at mile 1, and slowly degrade, mile by mile, until the final mile (or .2 of a mile) is at 9:00 + 10% pace.

[Edit] Ahh, I get you! that's the error! the last .2 miles should be 1.58!

Thanks )

Last edited: Oct 7, 2010

Upvote 0

DonkeyOte

MrExcel MVP

Oct 7, 2010

#4

I think Ruddles point was - to get the duration you should be multiplying result by (Dcurrent-Dprior) - last value thus being 0:01:59 (and total of 3:55:48)

Upvote 0

A

al_b_cnu

Well-known Member

Oct 7, 2010

#5

Thanks all, the penny did finally drop

Upvote 0

Ruddles

Well-known Member

Oct 7, 2010

#6

It would probably take me 9:54 to run 0.2 miles!

Upvote 0

A

al_b_cnu

Well-known Member

Oct 7, 2010

#7

I think I took that long to run the last .2 miles

Upvote 0

You must log in or register to reply here.

Similar threads

L

Question

Help with formula to keep leading zero in cell - cant format as date

Replies: 4

Views: 188

Dec 10, 2024

6StringJazzer

C

Question

Only Brings Unique Value Across

Replies: 3

Views: 177

Nov 18, 2024

kvsrinivasamurthy

K

J

Solved

Long IF Formula Calculation Trouble

Replies: 6

Views: 323

Aug 8, 2024

Alex Blakenburg

A

C

Solved

index match excel formula to vba code

Replies: 9

Views: 620

Sep 9, 2024

DanteAmor

V

Question

Adjust formula to calculate monthly dynamic average of work hours

Replies: 2

Views: 64

Feb 14, 2025

vladimiratanasiu

V

Share:

Facebook X Bluesky LinkedIn Reddit WhatsApp Email Link

Share this page

Facebook X Bluesky LinkedIn Reddit WhatsApp Email Link

Top