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...
Figure 1: Partial Pivoting involves swapping rows to avoid zero pivots.
The "Fatal Flaw" in Basic Code
In our previous post, Gauss Elimination (Part 2), we derived the mathematical foundation of the algorithm. However, if you implement that raw math directly into code (VBA, MATLAB, or C++), your program will eventually crash.
Why? Because the basic algorithm assumes the diagonal element (the pivot) is never zero. In the real world of engineering simulations, zeros happen frequently.
Advertisement
The Problem: Division by Zero
Let's look at a standard solvable system of equations:
The first step of the algorithm is to normalize the first row by dividing by the coefficient of x1 (which is 5). Since 5 is not zero, this works perfectly.
But what if the equations were rearranged?
Mathematically, the order of equations doesn't matter. But computationally, it changes everything. Look at this arrangement:
Here, the coefficient of x1 is 0. The computer attempts to divide by zero, resulting in a runtime error.
The Hidden Danger: Small Numbers
Even if the number isn't zero, but is very small (e.g., 0.000001), it causes Round-off Errors. Dividing by a tiny number creates a massive result, which can cause the computer to lose precision in the remaining digits. This is known as an "Ill-conditioned system."
The Solution: Partial Pivoting
To solve this, we use a technique called Partial Pivoting. Before performing the elimination step on a column, the algorithm must:
- Search: Look down the current column (below the diagonal).
- Identify: Find the row with the largest absolute value.
- Swap: Switch the current row with that row.
By ensuring the largest possible number is always in the pivot position, we avoid division by zero and minimize round-off errors.
Advertisement
Next Part: The Final Code
Now that we understand the logic and the safety features required, we are ready to build the robust Excel VBA tool.
Continue to Part 4:
Solving System of Equations using Gauss Elimination Method (Part 4: The Final Program)
Comments