Spoke Length Calculator Part 2

Welcome Back

(Note: I owe a massive thanks to my Dad for figuring out how to set up this equation. I couldn’t have done this with out him in 2010. Maybe I could today, but not nearly as fast! Thanks Dad, hope you enjoy. 🙂 )

You have now read the first post in this series and you know what we’re doing here. If you don’t know then consider this your refresher. We are here to do one thing and one thing only, determine spoke length. Why do we want to determine spoke length? To build bicycle wheels. Why do we want to build bicycle wheels? Because building wheels is what separates a mechanic from a craftsman, a student from a guru, and a lover from an aficionado. The wheels on the bike may go ’round and ’round (can’t read that without sing-songing it can you 🙂 ) but when you break a spoke, those wheels don’t go ’round no ‘mo. Mostly because they are shaped like an odd sort of ellipse.

Enter the wheelsmith, the person who will fix your wheels and get you back on the road. You will envy their prowess, their delicate application of power, tensioning each spoke within a tolerance of +- 1 nm. You will lust after the precision instruments that are the wheelsmith’s tools. Without question building wheels is a form of art. Just as determining proper spoke length a precision science.

Part Deux

It is time to automate our process for determining proper spoke length. If you solved the equation in part one more than 4 or 5 times I commend you. If you based your first purchase of custom spokes upon your calculation I applaud your bravery and hope you made no mistakes. The first time I bought spokes I did well and purchased the proper length. I commenced rebuilding the notorious wheel from part one with aplomb. The second set of wheels reminded me of my hubris. My pragmatic self stepped in to spare my ego and reminded me, ’twas not overconfidence that spoilt the lot, but rather a miscalculation of ones opponent. In this case, the spoke.

Damn you spoke! Days wasted awaiting your arrival, hours spent measuring components, money spent on 64 futile pieces of precision milling, all for naught. The miscalculated spokes were mere millimeters too short. My hubris felt a challenge that could be over come by deft overcompensation here, slight under compensation there, yes, yes, we can make this work!

No. No we can’t. It’s better I not recollect how long I fiddled and toiled with that wheel and those spokes, trying to make it work. I implore you reader, should you find yourself in a similar position, it would behoove you to accept your destiny and admit defeat. Of course I know you will not heed this advice, hell, I wouldn’t have either, but before embarking down the path of most resistance prepare yourself to become intimately familiar with the spoke wrench. Also be forewarned, two threads of nipple engagement is not, “engaged.” Alright, enough history, you will only go through the above once. If you go through it twice, watch “One Flew Over the Cuckoo’s Nest” and see if you relate more to Chief or Mac, you will need this understanding in your not so distant future.

To avoid mathematical errors the first step is to remove the human element. When performing repetitive tasks humans can’t be expected to be accurate much more that 80% of the time. I was taught this during a lean six sigma course and if I can find the source I will update this post. This is why you will not see mass production facilities with humans performing the operations. Sure, humans are on the line but they are running a machine that cannot operate outside a very limited set of constraints. What this means is the machine was designed with the understanding that this operator will make mistakes but when they do it will not allow the process to continue until they perform it right. I have witnessed this first hand at manufacturing facilities in the US and Mexico. It is impressive to say the least.

The machine we will use is the computer. The brains will be yours and the task master will be Microsoft Excel. I love Excel. It doesn’t make all the same mathematical mistakes I do. I am pretty good and figuring out the formula to use, I am not so good at executing that formula repeatedly with even 80% accuracy. If I were I wouldn’t have the fodder for the anecdote above. Go ahead and open up Excel and create a new file. Name it whatever you want, mine is called spoke_calc.xlsx. Your page will look like this if you are using a Mac. If you are using windows it will still look very similar.

Screenshot 2014-11-09 21.39.00

The next step will be to insert your variables. You will be providing data for your variables. It is of CRITICAL importance you measure these with precision accuracy which in this case in +-0.5 mm. The precision of this calculator is +-0.05mm meaning the result is calculated to the hundredths place.  When you go to purchase spokes they are usually sold in 1mm increments. Measuring with precision and calculating to the hundredths place will ensure your decision to round up or to round down is made with accuracy. After entering your variables and the magic field we are solving for your canvas should look like this.

Screenshot 2014-11-09 21.46.47

Now for the business. How do we enter the formula from the last post into Excel? I will tell you this much, all the beautiful LaTeX won’t help us here, but our beloved order of operations is just as relevant. Take a look at our variables. They are laid out in descending order as they appear in our spoke length formula all in column “A”. This means the value for each variable will be typed into column “B”.  For example, cell A1 contains the variable label Rim Radius, R= which means whatever you measure your rim radius to be, which corresponds to variable R in our formula will be entered in cell B1. You have probably figured out by now that by the time you get to the bottom and enter all your variables the cell across from SPOKE LENGTH, L= will be displaying your result, which is a fair assessment. But we still need to enter the formula to make all those variable mean something.

Excel doesn’t understand this formula as it is written here:

Spoke Length = sqrt {R^2 + H^2 + F^2 - 2{RH} \cos \frac {720}{h}\cdot X } - \frac {\phi}{2} \qquad

Excel speaks the same language but in a different dialect. We are going to calculate our formula the exact same way but we are going to speak in Creole rather than proper British English.

First order of business is to re-establish our variables. R is no longer Rim Radius. Why? Because rim radius is now stored in cell B1 which means R = the value entered into B1. This is why I have a love affair with Excel. Any value can be placed in B1, and Excel will solve accordingly. Here are our new variables.

R = B1  H = B2  F = B3  X = B4  h = B5  d = B6  L = B9

Now we need to write this beast in one line. Yep, that’s right, we are going to take all those steps from part one and write them on one line in one cell, B9. Excel handles the square root operation with syntax SQRT, the cosine operation with COS, and exponents with ^. We still need to tell Excel what order to operate and we do that through the use of () parentheses.

All formulas in Excel begin with an “=” sign. Remember the first thing I told you last time, take notice of where the “roof” of the SQRT sign ends. Almost all of the business happens beneath the roof so that is where most of our () will be. Since we are going to be taking the square root of everything underneath the roof lets start our formula like this:

= (SQRT B1^2 + B2 ^ 2 + B3 ^ 2 - 2 * B1 * B2 * COS 720 / B5 * B4 / 360 / 2 * 3.14159) - (B6 / 2)

Now we have two “blocks” one block being all that is under the roof and all that is outside. We still need to order the formula underneath the roof. As it is now will not return the correct answer. Lets follow the steps from last time and start with the COS operation. We want to take the COS of 720 / B5 * B4 / 360 / 2 * 3.14159. If we left it like this, following the order of operations working from left to right the formula would take the COS of 720 divide it by the value in B5 multiply that by the value in B4 divide that by 360 again by 2 and multiply it all by 3.1459 and give you a very wrong answer. Plug in 32 and 3 for B5 and B4 and you should get 0.956823 which is not right. This is what we need to get the correct answer of 0.382684.

= (COS((720/32*3)/(360/(2*3.14159))))

That was the hard part. In front of this we have 2 * B1 * B2 * the above. Tack that on to the front and you get.

= ( 2 * B1 * B2 * (COS ( ( 720 / 32 * 3 ) / ( 360 / ( 2 * 3.14159 ) ) ) )

The expression preceding this is B1^2 + B2 ^ 2 + B3 ^ 2 which can be solved by using just one set of () like so:

= ( B1 ^ 2 + B2 ^ 2 + B3 ^ 2 )

Join this with the previous include the ” – ” and you have

= ( ( B1 ^ 2 + B2 ^ 2 + B3 ^ 2 ) - ( 2 * B1 * B2 * ( COS ( ( 720 / 32 * 3 ) / ( 360 / ( 2 * 3.14159 ) ) ) ) ) )

Then we just include the SQRT encased inside ()

= ( SQRT ( ( B1 ^ 2 + B2 ^ 2 + B3 ^ 2 ) - ( 2 * B1 * B2 * ( COS ( ( 720 / 32 * 3 ) / ( 360 / ( 2 * 3.14159 ) ) ) ) ) )

tack on the bit from outside the roof and Voila! The final equation.

= ( SQRT ( ( B1 ^ 2 + B2 ^ 2 + B3 ^ 2 ) - ( 2 * B1 * B2 * ( COS ( ( 720 / 32 * 3 ) / ( 360 / ( 2 * 3.14159 ) ) ) ) ) ) - ( B6 / 2 )

Screenshot 2014-11-09 22.46.10

If you have your formula entered into cell B9 but don’t have any values plugged into the variable slots you will see a #DIV/0 error in cell B9. Don’t fret, it will go away as soon as you enter some values.

Screenshot 2014-11-09 22.45.48If we plug in the same values from the first installment,

Screenshot 2014-11-09 22.52.57

Now you are armed with enough information to become a spoke length calculating cyborg. Next time I will show you how to add this functionality to a user form using Microsoft Visual Basic for Applications otherwise known as VBA.

You have the knowledge, use it wisely grasshopper.

Until next time,

Ride and Write ON!

Will Conkwright