Bicycle Spreadsheet w calculations for Wind Drag and Road Grade

23 Jul

Some friends are doing RAGBRAI this summer, and one of them was asking about the differences between wind drag and road grade, so I put together a crude spreadsheet such that some scenarios could be run. Via my own experience, I’ve found that wind seems to be much more of a headache than a steep grade (granted, there are exceptions), so I wanted to see what some numbers might show.

The model is pretty crude, and a number of approximations were made for simplification purposes… Also, its not very user friendly, being I cranked it out so fast, thus a bit of textual explanation may prove helpful.

The key user entered variables are in light blue, namely total weight of the bike+rider+pack, windspeed, and the road grade. Windspeed is positive for a headwind, and negative values can be entered for tailwinds. The road grade or hill gradient is rise over run, with a typical max value of 0.12 rise/run which covers 95% of all roads.

The output of the model is located in the two columns names trained athlete and average Joe… which are color coded green, yellow, and red. Green corresponds to the ability to consistently maintain a give speed. Yellow is for short sprints, and red is pretty much impossible. The color shading changes based upon fixed values due to limitations of google docs speadsheet functionality.

The spreadsheet is open for anyone to modify, thus if you get some weird values, you may want to check the revision history, and revert back to the last version saved by me (mnphysicist).

You can access the spreadsheet at??

http://spreadsheets.google.com/ccc?key=0AjEwNba74LBgdE9OWEZVUVVZSVNGa3BtRFNkVUFfTEE&hl=en#gid=0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: