Seeking a practical layperson's explanation of the math behind spoke length selection for wheelbuilding I build bicycle wheels for a living. I am exceptionally frustrated with the quality and consistency of the calculation tools available for spoke lengths.
For those of you who don’t build wheels, let me mention that the spoke length needs to be accurate within a millimeter, (less than 1/3 of one percent). Otherwise you run the risk of spokes which are too long (pops your tire) or too short (unsightly and creates a weak wheel).
Compounding the task, manufacturing tolerances of rims, spokes, and hubs vary and they stretch and deform during building. With such a moving target, a tiny error in length calculation can become a time consuming failure. Here is the basic mathematical formula for spoke length determination:
$$ l = \sqrt {a^2+r{_1}^2+r{_2}^2-2r_1r_2 cos(\alpha)}$$
Where:

*

*a = distance from the central point of the hub to the hub flange, for example 30 mm. This is the base of the triangle that defines the length of the spoke in a radial pattern or zero cross pattern.




*r1 = spoke hole circle radius of the hub. This is the radius of the circle defined by the center of the spoke holes in the hub flange.




*r2 = nipple seat radius, equal to half the ERD of the rim. (ERD is effective rim diameter. It is the circle defined by the point where the spoken nipple contacts the rim. It is effectively the endpoint of the spoke on the rim end.)

*m = number of spokes to be used for one side of the wheel, for example 36/2=18 (Wheels are typically built with 24, 28, 32, or 40 total spokes, with half that number being used for each side of the wheel)

*k = number of crossings per spoke (This number is difficult to explain, but will be either zero, one, two or three. 95% of my wheels are 3X, with the remainder being 2X.  It refers to the number of times in a given spoke crosses over or under other spokes in the wheel. More crossings equal a stronger wheel because there is more crossbracing between spokes. More in-depth explanation here)

*α = 360° k/m

For most bicycle wheel builders, this is an intimidating formula.
I would like to see a practical demonstration of this formula in use. For a given hub and rim combination, how do I calculate spoke length using this formula?

An example data set would be:
A Rohloff Speedhub 500/14 is a fairly simple hub. It is a symmetrical
pattern hub. Both hub flanges have a circle diameter of 100mm $(r^1)$. The
manufacturer specifies a two cross lacing $(k)$ on this hub.
The center to flange distance on both sides of the hub is also
symmetrical, with a distance of 29mm $(a)$ from the center to each flange.
This particular hub and rim are drilled for 36 spokes $(m)$.


An ERD of 594mm $(r^2)$ for a Sun Ringle MTX33 rim completes the necessary
data.

As I recently built this wheel, I know that it requires a spoke length of 260mm.
However, given only this information, this formula, and a pencil and paper, I would not be able to calculate the correct spoke length. Please help me understand how to solve this equation given this data set.
In addition, I would like to know if it is possible, or rather how it is possible, to re-create this formula in an Excel spreadsheet or similar program. Any help is greatly appreciated.
 A: To get the length of the spoke correct to within $\ 1$mm, you need to take account of the effects of flange width and spoke diameter.  In the diagram below, I've added a picture of a spoke in blue to the figure you included in your question.  The centre of the little circle with cross-hairs lies at the intersection of the axes of the horizontal and vertical arms of the spoke. Your formula will give the distance from the centre of that circle to the tip of the spoke when $\ a\ $ is the distance indicated in the diagram.
Note that $\ a \ $ is smaller than the distance from the centre of the wheel to the centre of the flange.  The difference is half the width of the flange plus (approximately) half the width of the vertical arm of the spoke.  More precisely, if $\ \ell\ $ is the distance from the centre of the wheel to the centre of the flange, $\ w_f\ $ the width of the flange, and $\ d_s\ $ the diameter of the vertical arm of the spoke, then
$$
a=\ell-\frac{w_f}{2}-\frac{d_s\sqrt{r_1^2+r_2^2-2r_1r_2\cos\alpha}}{2\sqrt{a^2+r_1^2+r_2^2-2r_1r_2\cos\alpha}}\ .
$$
Unfortunately this is a fairly complicated equation that would have to be solved for $\ a\ $ to get its precise value.  Since $\ a^2\ $ is rather small relative to $\ r_1^2+r_2^2-2r_1r_2\cos\alpha\ $, however, the approximation
$$
a\approx\ell-\frac{w_f}{2}-\frac{d_s}{2}
$$
will probably be good enough.

Using the values of the quantities you give for the Rohloff Speedhub 500/14 and Sun Ringle MTX33 rim, we have
\begin{align}
\ell&=29\text{mm}\\
r_1&=50\text{mm}\\
k&=2\\
m&=18\\
\alpha&=\frac{2\times360}{18}^\circ\\
&=\frac{2\pi}{9}\,\text{radians}\\
r_2&=\frac{594}{2}=297\text{mm}\ .
\end{align}
From the technical specifications of the Rohloff Speedhub 500/14, I get
\begin{align}
w_f&=3.2\,\text{mm}\\
d_s&=2.7\,\text{mm}\\
a&\approx29-1.6-1.35\,\text{mm}\\
&=26.05\,\text{mm}\ ,
\end{align}
where I've taken the diameter of the vertical arm of the spoke to be the same as that of the spoke hole. Substituting these values into the formula for the supposed spoke length,
$$
\sqrt{a^2+r_1^2+r_2^2-2r_1r_2\cos\alpha}\ ,
$$
I get $\ 261.98$mm.  According to this web page, however, spoke length is typically not measured from the point at the centre of my cross-haired circle, but from "the inner side of the 90 degree bend.". This will be smaller than the length measured from the centre of the cross-haired circle by approximately the radius of the horizontal arm of the spoke. Taking this to be $\ 1.35\,\text{mm}\ $ as above, we finally get $\ 261.98-1.35=$$\,260.63$mm for the length of the spoke, which is within $\ 1$mm of your value of $\ 260$mm.
