Speed and Power Spreadsheet
******Changes as of May 25, 2007
I've included a nominal CdA in the Static Conditions section, and a line in Dynamic Conditions where you can adjust this... just to make it easier to tweak your CdA up or down without having to change each section.
Transmission losses are now a fixed number of watts rather than a percentage. Based on test data (Spicer et al) this should be closer to reality for the range of powers that cyclists normally use. 10W is a good ballpark unless you know better.
I now have a rider heading and a wind (from) direction rather than a wind angle relative to the rider... just thought it would be a little easier to use that way.
For riders who are looking at a course with a lot of elevation change, I've put together a sheet that is split up into 5 "blocks", so you can input an average elevation for each block. The sheet is the 5th one in the file, called "Big Climb".
I now total energy in KJ rather than W-hr because that is something people with power meters can relate to more easily.
I think that is about it. Also note that there are several different sheets in one file. You can easily create more by going to Insert/Worksheet and then copy and past an existing worksheet. Then you can keep all your calculations in one file.
*************
You can map out a course in great detail if you want. I only included 20 segments by default, but you can add more if you wish just by copying more columns... and be sure to change the summations in column A if you do that. But I never had an interest in using more than 20 segments... even if the course had a lot hills and turns, I'd just estimate how much of the the total course was at a particular grade and wind angle and call it good. As long as you don't do funny things like have more uphill than downhill, or more tailwind than headwind on a closed loop, you'll probably be close.
If you aren't sure what values to put in, I'll make a few suggestions:
Crr is the tire rolling resistance coefficient. It includes frictional dissipations in the tire as well as a fudge factor depending on the road roughness. A value of .0055 is a decent ballpark for good tires on an average road, but a smooth track will be better and chip seal will be worse.
CdA is the drag coefficient times the frontal area. This is a lumped value for the entire bike and rider. Obviously this is going to depend on your size, as well as your position, and the equipment you are using. If you are riding a standard bike and equipment but are low in the drops, a ballpark value would be CdA/kg^.67= .018. Or in other words if you weigh 75 kg (165 lb), CdA= .018*75^.67= .325. If you are sitting upright (like on a climb), CdA/kg^.67~ .024. If you have all the greatest TT equipment and good positioning, a CdA/kg^.67 value of .013 is possible.
The power vs altitude compensation factor is based on a study of elite runners. It assumes that the athlete has *not* had time to acclimate... but actually they found little difference between acclimated and non-acclimated performance. The equation used here is only good up to ~10,000 ft, so keep that in mind if you are trying figure out how long it will take you to ride to the top of Mt Everest.
Be conservative with the wind values... they aren't as great at bike level as they are at 10m up where the airport measures it. The ground effect slows it down, plus things like buildings, trees, and bushes will decrease it as well. A wind value of about half of the weather service value is a good estimate, but it can be more like 70% if it is very open ground.
Wind angle factor is based on "bluff body" aerodynamics and should be fairly accurate... except for parts that have an airfoil effect like aero frame tubes and deep carbon wheels and disks. In this case the real cross wind drag penalty will be lower than what I calculate here.
I hope you have fun with this. It took me a lot of time to put together, but I enjoyed doing it. If you have any questions or comments, send me an email.
