How to calculate average speed/pace?

pipps · Oct 4, 2014

I have a spreadsheet here which I am using to calculate my intended progress on an ultra-marathon that I am running in two weeks' time.

The distance for each stage is in Column B. In Column C, I first need to state my average pace. Based on the facts below, how should I calculate this?

From the start, I plan on running for 5 miles and then walking for 1 mile. When running, I will be travelling at a pace of 10:00 minutes per mile (min/mi) or 6.0 miles per hour (mph), and when walking I will be travelling at a pace of 15:00 min/mi or 4.0 miles per hour.

On this basis, how should I calculate my total estimated time taken to cover a given 6 miles at this combined pace please? What would be the formula to use?

Please forgive what must seem like a rather basic question, but I am not so numerically gifted as so many here. Thanks

wigi · Oct 4, 2014

Hello there

Welcome to MrExcel. This is far from basic. I would even say, very difficult.
I hope to come up with a solution.

Wigi

wigi · Oct 4, 2014

Okay, I got a solution but it uses VBA code for the main formula. Here are the steps to set it up:

1. insert a new column D
2. in cell C2, put: =IFERROR((B3-B2)/D2*60,0)
3. in cell D2, put: =fTimeNeeded(B2,B3)
4. in cell E2, put: =SUM($D$2:D2)
5. copy the formulas in the 3 cells downwards to row 17
6. add a new module in VBA: press Alt-F11, then choose Insert > Module in the menu. Paste this code in the white canvas:

Code:

Function fTimeNeeded(dblStartMiles As Double, dblEndMiles As Double) As Double    
    Const dblSpeedOfRunning As Double = 6 'number of miles per 60 minutes
    Const dblSpeedOfWalking As Double = 4 'number of miles per 60 minutes
    
    'for running
    For i = 0 To 100 Step 6
        
        'calculate overlap with chosen period
        dblOverlap = Application.Max(0, Application.Min(dblEndMiles, i + 5) - Application.Max(dblStartMiles, i))
        
        fTimeNeeded = fTimeNeeded + 60 * dblOverlap / dblSpeedOfRunning
        
    Next
    
    'for walking
    For i = 0 To 100 Step 6
        
        'calculate overlap with chosen period
        dblOverlap = Application.Max(0, Application.Min(dblEndMiles, i + 6) - Application.Max(dblStartMiles, i + 5))
        
        fTimeNeeded = fTimeNeeded + 60 * dblOverlap / dblSpeedOfWalking
        
    Next

End Function

7. Close VBA by the X icon of the window
8. Press F9 to recalculate the formulas
9. save the file as .xlsm since it contains VBA code

Can you get it to work?

Tyron · Oct 4, 2014

Hey Pipps,

You need to remember one thing in the Real World - The only dumb question is the one that IS NOT asked. Otherwise anything can be difficult.

Based on your worksheet sample that I saw you are in need of a couple good formulas to break down the milage into your two components (Running/Walking).

I am assuming that in column C (Average Pace) you want to indicate the amount of minutes that it will take you to complete a certain section (rows 1-18)? and in column D (time) you want to show what the estimated time you will start that section?

I am actually sensing the need for a UDF (User Defined Function) as you will have to break down the time into run minutes, walk minutes, and carry over time. I say carry over time as you may have to adjust the next section of your run to have more run or walk time based on the previous sections mileage.

For example in section 1 of the trip it the distance is 6.5 miles. If you only are running 5 miles and walk 1 mile at a time in that order then the first section would look like this:
Run : 5 miles = 8.33 minutes
Walk: 1 mile = 3.75 minutes
Run: .5 miles = .84 minutes
Total: 6.5 miles = 12.92 minutes = 12 minutes 55 seconds.

In addition to this as you have already started running .5 miles you must subtract that from one of the instances of 5 miles in the second section.

Section 2 is 12.5 miles
This means that it should look like the following:
Run: 4.5 miles = 7.52 minutes(-.5 miles from previous section)
Walk: 1 mile = 3.75 minutes
Run: 5 miles = 8.33 minutes
Walk: 1 mile = 3.75 minutes
Run: 1 mile = 1.67 minutes
Total: 12.5 = 25.02 minutes = 25 minutes

And this would continue. One way that might simplify this for you is if you added two columns which indicate run minutes and walk minutes.

This unfortunately is beyond my abilities, but one of the more advanced users here might be able to crush this math monster.

later

Ty

tusharm · Oct 5, 2014

If you run at 10 mins/mile, it will take you 50 mins. to cover the 1st 5 miles. Then, if you walk at 15 mins/mile, it will take you 15 mins. to walk the 6th mile. So, you will cover 6 miles in 50+15 or 65 mins.

pipps said:
I have a spreadsheet here which I am using to calculate my intended progress on an ultra-marathon that I am running in two weeks' time.

The distance for each stage is in Column B. In Column C, I first need to state my average pace. Based on the facts below, how should I calculate this?

From the start, I plan on running for 5 miles and then walking for 1 mile. When running, I will be travelling at a pace of 10:00 minutes per mile (min/mi) or 6.0 miles per hour (mph), and when walking I will be travelling at a pace of 15:00 min/mi or 4.0 miles per hour.

On this basis, how should I calculate my total estimated time taken to cover a given 6 miles at this combined pace please? What would be the formula to use?

Please forgive what must seem like a rather basic question, but I am not so numerically gifted as so many here. Thanks

wigi · Oct 5, 2014

Tyron said:
Run: 1 mile = 1.67 minutes

This is really world record speed...

Even with a bike it's very fast.

Tyron said:
but one of the more advanced users here might be able to crush this math monster.

I wrote a UDF, I am waiting for the feedback of the OP.
I started off with regular Excel functions and array functions, but it became too difficult at the one hand, and not maintainable/comprehensible to the OP on the other side. Therefore, a UDF.

pgc01 · Oct 5, 2014

Hi

This is a formula solution.

In C3: =(B3-B2)/(D2-N(D1))/24
In D3: =INT(B3/6)*"0:65"+MIN(B3-FLOOR(B3,6),5)*"0:10"+MAX(B3-FLOOR(B3,6)-5,0)*"0:15"

I formatted the values in column C as Number with 3 decimals and the values in column D as "h:mm:ss"

Copy down

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;text-align:left;border: solid 2px #000000; ">Name</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;text-align:left;border: solid 2px #000000; ">Mile</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;text-align:center;border: solid 2px #000000; ">Av pace</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;text-align:center;border: solid 2px #000000; ">Target time</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;text-align:center;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Start: Goring</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.571</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">1:10:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 1: Wallingford</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">6.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.538</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">2:15:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 2: Little Wittenham</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">12.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.538</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">3:20:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 3: Wallingford</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">18.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.571</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">4:30:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 4: Goring</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">25</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">6.000</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5:10:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >7</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 5: North Stoke</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">29</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.368</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">6:45:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >8</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 6: Swyncombe</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">37.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.667</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">8:15:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >9</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 7: North Stoke</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">46</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.333</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">9:00:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >10</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 8: Goring</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">50</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.659</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">10:28:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >11</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 9: Bury Downs</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">58.3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.362</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">11:15:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >12</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 10: Chain Hill</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">62.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.362</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">12:02:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >13</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 11: Bury Downs</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">66.7</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.659</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">13:30:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >14</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 12: Goring</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">75</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.333</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">14:15:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >15</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 13: Whitchurch</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">79</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.667</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">15:45:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >16</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 14: Reading</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">87.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.486</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">17:12:30</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >17</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Aid 15: Whitchurch</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">95.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">5.684</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">18:00:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >18</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border: solid 2px #000000; ">Finish: Goring</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; ">100</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border: solid 2px #000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >19</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=6 style="background:#9CF; padding-left:1em" > [Book2.xlsm]Sheet1</td></tr></table>

Tyron · Oct 5, 2014

wigi said:
This is really world record speed... Even with a bike it's very fast.

I wrote a UDF, I am waiting for the feedback of the OP.
I started off with regular Excel functions and array functions, but it became too difficult at the one hand, and not maintainable/comprehensible to the OP on the other side. Therefore, a UDF.

Hey wigi,

Thanks for pointing out the mis-calculation. lol. Yeah I read it over and was going to edit it, but unfortunately for me to much time had passed before I was able to get back to it. So, edit not allowed. lol.

I saw your UDF and am waiting also to see if it works out. Looks good though. Would you be able to put comments in to let me know what each line is doing. I am new to VBA and would like to add to my learning. Thanks in advance.

later

Ty

wigi · Oct 5, 2014

Hello

Sure.

Code:

Function fTimeNeeded(dblStartMiles As Double, dblEndMiles As Double) As Double        
	'constants to store the speed of both activities
    Const dblSpeedOfRunning As Double = 6 'number of miles per 60 minutes
    Const dblSpeedOfWalking As Double = 4 'number of miles per 60 minutes
    
    'for running
	'let's loop over the full distance, and set the patterns of 5 miles running and 1 mile walking
	'I use from 0 to 100 even though that range is too wide (see the 2 parameters dblStartMiles and dblEndMiles)
	'in practice, we won't notice the speed difference and the code remains easy
    For i = 0 To 100 Step 6
        
        'calculate overlap with chosen period
		'i goes from 0 to 6 to 12 to 18 and so so on
		'so i + 5 is the end of each running part
		'let's calculate the overlap with the period specified by the 2 parameters
		'the MAX with 0 is done to avoid negative time periods
        dblOverlap = Application.Max(0, Application.Min(dblEndMiles, i + 5) - Application.Max(dblStartMiles, i))
        
		'calculate time based on distance in miles divided by speed
        fTimeNeeded = fTimeNeeded + 60 * dblOverlap / dblSpeedOfRunning
        
    Next
    
    'for walking
	'same as above but now for walking
    For i = 0 To 100 Step 6
        
        'calculate overlap with chosen period
		'i + 6 is the end of each walking activity and walking itself starts at i + 5
        dblOverlap = Application.Max(0, Application.Min(dblEndMiles, i + 6) - Application.Max(dblStartMiles, i + 5))
        
        fTimeNeeded = fTimeNeeded + 60 * dblOverlap / dblSpeedOfWalking
        
    Next


End Function

wigi · Oct 5, 2014

That's a very nice formula approach pgc ! Well done.

How to calculate average speed/pace?

pipps

New Member

Excel Facts

wigi

Well-known Member

wigi

Well-known Member

Tyron

Active Member

tusharm

MrExcel MVP

wigi

Well-known Member

pgc01

MrExcel MVP

Tyron

Active Member

wigi

Well-known Member

wigi

Well-known Member

Similar threads

Forum statistics

Share this page

How to calculate average speed/pace?

New Member

Excel Facts

Well-known Member

Well-known Member

Active Member

MrExcel MVP

Well-known Member

MrExcel MVP

Active Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock