Bikeerg pace/distance spreadsheet help

[bullitt0347]

I’m trying to put an excel spreadsheet together for the time it will take me to cover ‘x’ distance at ‘x’ pace. Quite easy on the Bike as the time is per 1,000m – or so I thought!!

So far, I’ve done 2 timed pieces a 4k and a 40k. when I looked at the 4k pace/1,000 under the 4,000, column hey presto it came out at 06:55.2 which was my time.

However, when I look down the 40k column at the 01:57.1 pace I get 01:18:04.0 yet my actual time according to my ErgData app was 01:18:07.8.

I have the column set at ‘hh:mm:ss.0’ on the format section.

The reason I’m doing this is because I figured like the RowErg before it will give me an idea of pace when I’m setting out on other timed pieces (whether they are ranked or my own challenges)

Hope this make sense!!

Any ideas?

Pete

(Hello by the way, I've been a keen Concept rower for years (20 million metres and counting) but due to an injury I had to swap out to the Bike recently, really enjoying it. Thought recently I have been getting back on the rower doing some very gentle short distances)

[Nomath]

Welcome to the forum!
I don't fully understand by what formula you want to calculate that spreadsheet table.

My approach is to use the power law formula, which is often used in engineering and in the analysis of sport performances.
The formula is simple and contains only one adjustable : Time(X)/Time(Xo) = (X/Xo)↑a
Here X is a distance for which you want to predict the time. Xo is a reference distance for which you know the time.
The exponent a is a characteristic describing the 'fatigue factor'. This exponent is only slightly depending on the sport and the person who is doing the exercise. It is generally below 1.10, except when the athlete cracks at long distances.

Let's analyse your data, if I understand them correctly. Your actual time at 4K is 6:55.2, that is 415.2 sec.
Your actual time at 40K is 1:18:07.2, that is 4687.8 sec.
The ratio of the distances X/Xo = 10, so the power exponent is the 10-logarithm of 4687.8/415.2 = 11.29, i.e. 1.053

In 2019 I did a similar analysis of data in the C2 BikeErg rankings. I sampled 12 individuals that listed both their seasonal PB at 4K and 40K (there are probably a lot more, but I didn't know how to find them). The power law exponents ranged between 1.04 and 1.16. The value of 1.16 is clearly an outlier, as the next highest value was only 1.10. The average was close to 1.06.

I would interpret individual exponent values as : below 1.06 are typical for endurance cyclists , above 1.06 are typical for short-track cyclists.

Interestingly, for the SkiErg (I sampled 10 individuals), I also found an average exponent close to 1.06.
For the RowErg, the average exponent was near to 1.08.

Does this adress your question?

[bullitt0347]

Yes it does, thank you for the reply.

Sorry for delay in replying - i was off on my hols. Needless to say put a few pound on - so back on the bike with a vengeance now!!!

[Nomath]

I’m trying to put an excel spreadsheet together for the time it will take me to cover ‘x’ distance at ‘x’ pace....

Nice that it helps.

I forgot to directly answer your first point : what is the pace P to cover a distance X, knowing your pace P(Xo) for covering a distance Xo ?
Rewriting the power law formula for pace : P(X) = P(Xo) * (X/Xo)↑(a-1)

For example : your time to cover 5K was 415.2 sec. Your time to cover 50K was 4687.8 sec. The calculated exponent a = 1.053
The (average) pace at 5K is 83.0 sec. The pace at 50K is 83.0 * 10↑(0.053) = 93.8 sec.
This result seems fairly trivial because it obviously equals 4687.8/50
However, the formula allows you to calculate the pace at e.g. 2K (83.0*(2/5)↑0.053 = 79.1 sec), at 10K (86.1 sec) or at 40K (92.8 sec).

[Ombrax]

However, when I look down the 40k column at the 01:57.1 pace I get 01:18:04.0 yet my actual time according to my ErgData app was 01:18:07.8.

I just double-checked your numbers, and your spreadsheet time (1:18:04.0) is correct for a 40k at 1:57.1 / 1k.

[ (1+57.1/60)/1k ] x 40k = 78.066666 minutes = 01:18:04.0

Therefore, ErgData is off, and somehow must have found an extra 3.8 seconds. That's really not that much, and I bet it's due to round-off error in your pace - ErgData probably used something slightly less than 1:57.1 in the actual calculation, which results in a longer time. Your spreadsheet, where I assume you increment the pace in 0.1 second steps, is using a number accurate to more decimal places.

[JaapvanE]

That's really not that much, and I bet it's due to round-off error in your pace - ErgData probably used something slightly less than 1:57.1 in the actual calculation, which results in a longer time. Your spreadsheet, where I assume you increment the pace in 0.1 second steps, is using a number accurate to more decimal places.

That is a big issue I escalated to C2 a couple of weeks ago: they are too conservative/rigorous in their rounding, thus leading to inaccurate data when used in post-processing. It seems to originate in ErgData, as ErgZone delivers more precise results. It might help if more people drop a similar complaint.

[Ombrax]

OK, here are the numbers Ergdata must be using assuming what we know for distance and time in Ergdata:

Assuming: Distance = 40k and Time = 1:18:07.8 = 78 + 7.8/60 = 78.13 min

Rate = 40k/78.13 and so Pace = (78.13 / 40k) x 1000m = 1.95325 min / 1k = 1:57.195

As opposed to the 01:57.1 used by the OP's spreadsheet.

So, a difference of about 0.1 seconds between the two. Not a huge deal IMO, but that would explain the difference between the two.

[Nomath]

....
For example : your time to cover 5K was 415.2 sec. Your time to cover 50K was 4687.8 sec. The calculated exponent a = 1.053
The (average) pace at 5K is 83.0 sec. The pace at 50K is 83.0 * 10↑(0.053) = 93.8 sec.
This result seems fairly trivial because it obviously equals 4687.8/50
However, the formula allows you to calculate the pace at e.g. 2K (83.0*(2/5)↑0.053 = 79.1 sec), at 10K (86.1 sec) or at 40K (92.8 sec).

Sorry for this sloppy part of my post. The power law formula for the (1K) pace is correct, but I took the wrong distances in the calculations. The calculated pace at 4K was wrong, as others have pointed out.
Here is the corrected version :

For example : your time to cover 4K was 415.2 sec. Your time to cover 40K was 4687.8 sec. The calculated exponent a = 1.0527
The (average) pace at 4K is 103.8 sec.
The formula enables you to predict the pace at any other distance, e.g. at 2K (103.8*(2/4)↑0.0527 = 100.1 sec), at 10K (108.9 sec) or at 50K (118.6 sec).

[JaapvanE]

My approach is to use the power law formula, which is often used in engineering and in the analysis of sport performances.
The formula is simple and contains only one adjustable : Time(X)/Time(Xo) = (X/Xo)↑a
Here X is a distance for which you want to predict the time. Xo is a reference distance for which you know the time.
The exponent a is a characteristic describing the 'fatigue factor'. This exponent is only slightly depending on the sport and the person who is doing the exercise. It is generally below 1.10, except when the athlete cracks at long distances.
...
For the RowErg, the average exponent was near to 1.08.

Maurice,

Would you consider this a better approximation than Paul's law, with its targetPace = originalPace + (5 * Log2(targetDistance / originalDistance)?

Jaap

[Nomath]

Would you consider this a better approximation than Paul's law, with its targetPace = originalPace + (5 * Log2(targetDistance / originalDistance)?

I believe that the power law is preferable.

For readers who are less familiar with mathematical formulas, I like to first point out an important difference: Paul's law increases the pace by a constant amount of time when you double the distance. The power law increases the pace by a proportional amount of time when the distance is doubled. Moreover, the proportionality factor can be adjusted to the characteristics of individual athlete and to the sport.

Let's consider a pretty strong rower who completes the 2K distance in 7m00s, i.e. a 500m pace of 1m45s. We will take (2K, 105s) as the base point for comparing the formulas.
The target pace at other distances using Paul's law is shown by the red curve in the attached graph (vertical axis : Pace in sec").
The blue curve is the target pace for this rower using the power law with the exponent a=1.08 in (X/Xo)↑(a-1) .
The green curve is the target pace for a=1.06.
To keep the chart clear, I didn't add the curve for a=1.65, but it practically overlaps the red curve of Paul's law.



So the power law can accomodate Paul's law, but it can also be adjusted to athletes who are more endurance-type (lower values of a) or short-track type (higher a values)

The chart looks somewhat different when I had taken a less strong rower who completes the 2K distance in 8m00s, i.e. a pace of 120 s. Paul's law doesn't discriminate between strong and less-strong rowers. The less-strong rower should also increase his target pace by 5 sec when the distance doubles. My intuition is that the increase should be higher for less-strong individuals. The power law does this.

I analyzed the data from ice speed skating world championships for 1500m, 5K and 10K. Typical finishing times are 1m45s for 1.5K, 6m for 5K, 13m for 10K. For these world class athletes, the a-value is typically around 1.03.

[JaapvanE]

So the power law can accomodate Paul's law, but it can also be adjusted to athletes who are more endurance-type (lower values of a) or short-track type (higher a values)

I agree with you in principle, but I've played around with changing the 5 in Paul's law to a 4.5, which led to interesting results. What is your opinion on that one?

[Nomath]

It goes against my intuition that a strong rower has to increase his target pace by the same amount as a much weaker rower when the distance is doubled. Adding 4.5 sec instead of 5 sec doesn't address this point.

I take the opportunity to correct a mistake in my last post : the a-value for which the power law curve practically overlaps Paul's law curve in the above graph is 1.065, not 1.65.