For engineers who already know the math—but still lose projects. For the last few years, I’ve been sharing technical guides here on Mechanical Design Handbook —how to size a motor, how to calculate fits, and (as you recently read) how to choose between timing belts and ball screws. But after 25 years in industrial automation, I realized something uncomfortable: Projects rarely fail because the math was wrong. They fail because: The client changed the scope three times in one week. A critical vendor lied about a shipping date (and no one verified it). The installation technician couldn’t fit a wrench into the gap we designed. University taught us the physics. It didn’t teach us the reality. That gap is why I wrote my new book, The Sheet Mechanic . This is not a textbook. It is a field manual for the messy, political, and chaotic space between the CAD model and the factory floor. It captures the systems I’ve used to survive industrial projec...
Problems in mechanical design often require the solution of nonlinear equations. A classic example is finding the roots of a polynomial stress function:
3x3 - 20x2 + 1000x + 12000 = 0
These are known as root solving problems: finding the value of x where f(x) = 0. While you could solve this by trial and error (guessing values until the result is zero), or use the mathematical Newton-Raphson method, there is a faster way for the practicing engineer.
Advertisement
In this post, I will share a simple technique to solve these problems instantly on your desktop using Microsoft Excel Goal Seek. No VBA programming or expensive MATLAB license is required.
Step-by-Step: Using Goal Seek for Engineering
1. Setup the Spreadsheet
Open Excel and set up two cells: one for your input variable (x) and one for your equation (f(x)). The formula cell must mathematically reference the input cell.
2. Locate the Tool
In modern Excel versions, navigate to: Data Tab > Forecast Group > What-If Analysis > Goal Seek...
3. Configure the Parameters
A dialog box will appear. To find the root of the equation, we want to force the result to be zero.
- Set cell: The cell containing your equation (f(x)).
- To value: Enter 0.
- By changing cell: The cell containing your variable (x).
4. The Solution
Click OK. Excel iterates through values instantly. It will find the exact value of x that satisfies the equation. Note that due to computer floating-point precision, the result might appear as "0.0001", which is effectively zero for engineering tolerances.
Advertisement
Application: Shaft Design for Fatigue Loading
Real-world mechanical design is rarely linear. Consider designing a transmission shaft under fatigue loading. The relationship between diameter (d) and safety factor (n) often follows the rigorous ASME Elliptic or DE-Goodman criteria.
Solving for the diameter (d) algebraically is difficult because it appears inside cubic roots and squares. Here is the standard equation form:
1/n = (16 / Ï€d3) × [ (4(Kf Ma)2 + 3(Kfs Ta)2)1/2 / Se + (4(Kf Mm)2 + 3(Kfs Tm)2)1/2 / Sut ]
Instead of rearranging this massive equation to isolate d, you can simply:
- Set up cells for all inputs (Ma, Ta, Se, etc.).
- Create a cell for the Left-Hand Side (1/n).
- Create a cell for the Right-Hand Side (The stress terms).
- Create a "Check Cell":
= LHS - RHS. - Use Goal Seek to force the "Check Cell" to 0 by changing the Diameter cell.
This reverse-engineering approach gives you incredible flexibility. You can just as easily fix the diameter and solve for the maximum allowable Moment (Mm) without rewriting a single formula.
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 !!