How to Design a Hoeken’s Straight-Line Linkage in Excel (with VBA Simulator) Figure 1: Geometry of the Hoeken’s straight-line linkage and resulting coupler-point trajectory. The lower portion of the curve approximates straight-line motion over ~180° of crank rotation. The Hoeken’s Linkage is a mechanical engineer's favorite magic trick. It is a four-bar mechanism that converts simple rotational input into a near-perfect straight-line output. Unlike the Watt Linkage (which traces a figure-8), the Hoeken’s Linkage creates a "tear-drop" shape with a long, flat bottom (see Figure 1). This makes it the standard choice for walking robots and intermittent linear actuators. But how do you find the link lengths? If you guess, you get a wobble. This guide provides practical "Golden Ratios" and an Excel VBA tool to simulate the motion path. 1. The Geometry: Practical Design Ratios To achieve a usable straight line, link lengths m...
Problems in mechanical design often require the solution of nonlinear equations such as:
3x3 - 20x2 + 1000x + 12000 = 0
These problems are stated in the form: find the zeros or roots of the function f(x); that is, find the values of x that make f(x) = 0. Therefore, they are sometimes called root solving problems. The equation shown above is typical of those encountered when finding maximum stress at a point.
While such equations can be solved by trial and error (guessing values until the left-hand side is nearly zero), there exist simple, systematic ways to solve them, such as the Method of False Position and the Newton-Raphson method.
Root Finding Methods
The Method of False Position systematically narrows down the region in which the root exists. The Newton-Raphson method is more sophisticated, using the slope of the function (derivative) to estimate the root's location. For most functions, Newton-Raphson converges much faster and is generally the method of choice.
In this post, I will share a very simple technique to solve these problems easily on your desktop PC using Microsoft Excel Goal Seek. No VBA programming knowledge is required.
Microsoft Excel: Goal Seek Technique for Mechanical Engineering Design
1. Open Microsoft Excel and enter the value and formula as shown below:
Ensure you have a cell for your input variable (x) and a cell for your equation (f(x)). The formula in the f(x) cell must reference the x cell.
2. Navigate to the Goal Seek Tool
In modern versions of Excel (2010 and later), go to the Data tab on the Ribbon. Look for the Forecast group (or Data Tools group in older versions), click on What-If Analysis, and select Goal Seek...
3. Configure the Goal Seek parameters:
A dialog box will appear. Fill it out as follows:
- Set cell: Select the cell containing your formula (e.g., E16).
- To value: Enter 0 (since we are solving for f(x) = 0).
- By changing cell: Select the cell containing your variable x (e.g., E14).
Then click OK.
4. Review the result:
Excel will iterate to find the solution. If successful, it will display the Goal Seek Status. Note that the "Current value" might not be exactly zero (e.g., 0.0001) due to computational precision, but this is usually sufficient for engineering tolerances.
Application: Shaft Design for Fatigue Loading
Consider designing a shaft under fatigue loading. Often, the relationship between diameter (d) and safety factor (n) is complex and cannot be solved algebraically.
For example, if you want to know the maximum bending moment (Mm) a shaft can withstand given a known diameter:
- Set up cells for all input factors (d, n, Ma, Ta, etc.).
- Create a cell for the Left-Hand Side (LHS) of the design equation.
- Create a cell for the Right-Hand Side (RHS).
- Create a difference cell:
= LHS - RHS. - Use Goal Seek to set the "Difference" cell to 0 by changing the Mm cell.
This method gives you flexibility. If you later decide to fix Mm and find the required diameter instead, just change the "By changing cell" to the diameter cell.
For more complex optimization problems involving constraints, check out Microsoft Excel Solver, which is a more powerful add-in for engineering analysis.
Recommended Reading: Excel for Scientists and Engineers
Comments
I need to generalize this equation solver thing for some calculations, so that, i can get the solution every time on changing value of coefficient.
for example;
in a cubic equation, if i change coefficient a,b,c,d then also, i can get the solution.
Please help.
Thanks !!