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...
The Backbone of Engineering Simulation
In mechanical engineering, solving a system of linear equations is perhaps the most fundamental calculation we perform. Whether you are running a Finite Element Analysis (FEA) to check stress concentrations or a Computational Fluid Dynamics (CFD) simulation, the computer is ultimately solving a massive system of equations in the form of [A]{x} = {B}.
The Gauss Elimination Method is a classic algorithm used to solve these systems. Unlike Cramer's Rule, which is inefficient for large matrices, Gauss Elimination scales well for complex engineering problems.
Advertisement
Step 1: Setting up the Matrix
To solve a system of equations in Excel, we first organize our coefficients into a matrix format.
As shown in the figure below, the coefficients of the variables (x, y, z) form the [A] Matrix, while the constants on the right side of the equal sign form the {B} Vector.
Figure 1: Initial setup of the Coefficient Matrix [A], Constant Vector {B}, and the resulting Solution Vector.
Step 2: Forward Elimination and Back Substitution
The algorithm works in two stages:
- Forward Elimination: We manipulate the rows to convert the [A] matrix into an Upper Triangular Matrix. This means all elements below the main diagonal become zero.
- Back Substitution: Once the last variable is isolated, we solve for it and substitute the value back up the chain to find the remaining variables.
Figure 2: The logic flow: transform to upper triangular, then back-solve.
The result of this process is shown in the figure above. Notice how the calculation identifies the precise values for X1, X2, and X3 that satisfy all equations simultaneously.
Excel VBA Code Implementation
While you can perform these steps manually in Excel cells, using VBA (Visual Basic for Applications) is much more powerful for repetitive design tasks.
Below is a custom function you can use. To install it:
- Press
Alt + F11to open the VBA Editor. - Go to Insert > Module.
- Paste the code below.
Option Explicit
' Function to solve linear equations Ax=B using Gauss Elimination
Function SolveLinearSystem(coeffMatrix As Range, constMatrix As Range) As Variant
Dim n As Integer
Dim i As Integer, j As Integer, k As Integer
Dim factor As Double
Dim a() As Double
Dim b() As Double
Dim x() As Double
Dim sum As Double
n = coeffMatrix.Rows.Count
' Resize arrays
ReDim a(1 To n, 1 To n)
ReDim b(1 To n)
ReDim x(1 To n)
' Read data from Excel ranges to arrays
For i = 1 To n
For j = 1 To n
a(i, j) = coeffMatrix.Cells(i, j).Value
Next j
b(i) = constMatrix.Cells(i, 1).Value
Next i
' --- Forward Elimination ---
For k = 1 To n - 1
For i = k + 1 To n
If a(k, k) = 0 Then Debug.Print "Error: Pivot is zero!"
factor = a(i, k) / a(k, k)
For j = k To n
a(i, j) = a(i, j) - factor * a(k, j)
Next j
b(i) = b(i) - factor * b(k)
Next i
Next k
' --- Back Substitution ---
x(n) = b(n) / a(n, n)
For i = n - 1 To 1 Step -1
sum = 0
For j = i + 1 To n
sum = sum + a(i, j) * x(j)
Next j
x(i) = (b(i) - sum) / a(i, i)
Next i
' Return result as a vertical array
SolveLinearSystem = Application.WorksheetFunction.Transpose(x)
End Function
You can now use this function in any cell by typing =SolveLinearSystem(A_Range, B_Range).
Advertisement
Next Part
Continue to the advanced application in the next post:
Solving System of Equations using Gauss Elimination Method (Part 2)
Comments