Spreadsheet to estimate speed based on power?

Hello, I'm trying to find a spreadsheet that will allow me to predict what speed I will do at a given power. I can find quite a lot of online calculators, I can also find some power formula written down, I can also find spreadsheets that allow me to estimate power for a given speed but can't find any spreadsheet that predicts speed. Can anyone point me in the right direction?

The reason for wanting this is that I am riding L'Etape in the summer and wanted to have a go at predicting how long it will take. I was planning to do that by plotting the elevation of the route by distance and then creating a look up table that says what power I will aim for at a particular slope and elevation and use that to give my speed. I realise it won't be accurate but I'm interested to know how close to eight, nine, ten or eleven hours it will take me.

I was then thinking about modifying the power table to see what kind of strategy would be most efficient.

Thanks

 

The best power/speed calculator I have seen is AnalyticCycling http://www.analyticcycling.com/. This site also has a comprehensive set of tools to estimate the impact of position, wheels, tires, etc. I have found the AnalyticCycling calculator to be more accurate than the Kreuzotter calculator http://www.kreuzotter.de/english/espeed.htm. But, beware of the importance of the assumptions you input. Other than weight, it's very difficult to estimate the other key input requirements to get an accurate predicted speed. For example, rolling resistance is huge and it's difficult to find a good rolling resistance number for a specific set of tires. You might want to benchmark the power/speed calculator on a known course. Good luck finding a flat course and even miniscule differences in grade (<1%) have a huge impact on speed. You need to keep in mind that a bike could go very fast without much power if it weren't for a little inconvenience known as resistances (e.g., gravity, air, rolling). The highly aerodynamic HPVs get ~40mph on 200W of power! http://www.adventuresofgreg.com/24/index.html

 

Thanks RapDaddyo. I've found the analytic cycling site before but can't find a spreadsheet that I can download (the site is down at the moment so I can't double check it). I've also found the kreuzotter site before and this is the one that I've got the formula on. I was just being lazy as I didn't really want to code those formula as they look quite complicated and I could quite easily make a mistake. I guess I am going to have to stop being lazy unless someone else knows of one.

 

I just don't know why you would want to reinvent the wheel. The formulas aren't terribly complex, but the implementation is. Unless you're going to be running the model to produce different sorts of results (e.g., a TT pacing optimization model), I'd recommend learning the AC interface and running your "what-if" analyses there. Again, the kreuzotter site has a nice user interface but I have found the results to be seriously at odds with what I get with my PT SL. IOW, I can't look under the covers at what's going on internally so I can only base my confidence in the model based on it producing results similar to what I see on my bike on climbs with a known grade.

 

here are the ones I use; one has input (in green) for speed, grade, etc. and the other has input (in green) for distance, time, and elevation gain and calculates speed and grade.

 

Well here's the physics expressed in maths terms courtesy of Andy Coggan's presentation on the demands for pursuit riders.

I'd just use Analytic Cycling - they've done all the hard work for you!

 

Forecasting the wind speeds and directions may be a tricky item, and are you drafting or riding in the front?

 

Thanks, but this is one that again estimates power from speed rather than the other way around.

 

That's a much better description of the formula, especially as it gives the units. I think I'll be using this!

 

I want to re-invent the wheel, because I want to use it offline rather than online (I spend 1hr 40m on the train each day). I also want to be able to do "what-if" analysis to see how things change over the whole course. I have never done a bike ride up five mountains (I haven't even done one mountain) so I want to understand what happens if I put in a power of x going up a mountain and see how it changes NP compared with power y (and I wonder if there is a formula for adjusting NP to take account of altitude because altitude appears to change FTP).

 

For now, I am going to assume that I am going by myself on a windless day! In reality, I will draft where I can.

 

I've just had another look at this and it is written in terms of power rather than in speed. Unfortunately, my maths is not good enough to solve that equation in terms of speed. So, back to square one.

 

I'm yet to get my PT (and just dying to find out figures etc.) so I used their tool to work out the power used over a segment of a race of which I remember the figures.

I must be doing something wrong because it tells me that the power value is 376W.............that can't be right.

I used the default values given for Frontal area, drag, air density, rolling res, slope, effective pedaling range and substituted weigth (80kg with bike), Speed (9.38m/s), Cadence (90), and Crank Length (175mm).

Surely I couldn't have made a mistake with so few value requirements?? or am I just too dumb to get it ??

 

Gianni,

Unless you know your CdA to high precision, you won't get very good results. The exception is for a steep climb where speed is low and the aerodynamic component is negligible. It's best if it's a steep, steady climb with known grade.

Tri Trigger,

Sorry I didn't read the OP well, but to solve for speed is obviously much harder. However, all these calculations are really meant for instantaneous results, not for constantly changing conditions. Just the small changes in position that you make by the minute will confound the situation.

 

No probs. Thanks.

I wil ahev to wait for the my PT....

 

That's definitely the easiest way.

In bocca al lupo

 

The reason it's difficult to get speed from power is that it requires you to solve a cubic equation of the general form as^3+bs^2+cs-P=0, where s is speed and P is power. a,b &c relate to gravity, mass, friction, incline, drag coefficient etc.

This isn't a trivial problem. But see here for some pointers. Good luck !

 

Two words: LOOKUP table.

 

Thanks Andy. That's what I have used over the last few days. Over the weekend I was going to see how easy it would be to create an Excel macro that used the goal seek function to do it.

 

I use the solver function within Excel (look under the tools menu). This is quicker than writing a macro.