Skip to main content

Featured Post

Why I Wrote The Sheet Mechanic (And Why Calculations Aren’t Enough)

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...
Post a Comment
NEW RELEASE: Stop trying to be a Hero. Start being a Mechanic. Get "The Sheet Mechanic" on Amazon »
Disclosure: As an Amazon Associate, I earn from qualifying purchases.

Solving System of Equations using Gauss Elimination Method (Part 1)

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}.

User interface for Excel VBA Gauss Elimination solver

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.

Matrix setup showing Coefficient Matrix A and Constant Vector B

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:

  1. 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.
  2. 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.
Flowchart showing Matrix Transformation to Upper Triangular form
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:

  1. Press Alt + F11 to open the VBA Editor.
  2. Go to Insert > Module.
  3. 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)

References

⚡ Industrial Automation Guide

Designing heavy-duty machinery? Ensure your calculations match the real-world load:

Labels:

Comments

Post a Comment

Popular posts from this blog

Roberts straight-line mechanism

Figure 1: A modern linear ball slide (like this THK model) is the contemporary solution for precise straight-line motion. Many modern engineering applications require components to move in a precise linear fashion, known as " straight-line motion ". Today, we take this for granted. We can simply purchase an off-the-shelf Linear Motion Guide that moves a device accurately along a rail with low friction. The Historical Challenge: Making a Straight Line However, in the late 17th and early 18th centuries—before the development of high-precision milling machines—it was extremely difficult to manufacture long, perfectly flat surfaces. Creating a sliding joint without significant backlash was nearly impossible. During that era, engineers had to rely on Linkages . Much thought was given to the problem of attaining a straight-line motion using only revolute (hinge) connections, which were much easier to manufacture. The most famous early result was...
Post a Comment
Read more about this Mechanical Design Article »

Ball Detent Torque Limiter – Working Principle & Selection

Figure 1: The ball detent mechanism provides precise overload protection by disengaging instantly when the torque limit is exceeded. The First Line of Defense: Overload Clutches In high-speed automation and heavy industrial machinery, a "jam" is not a matter of if , but when . Whether it is a cardboard box getting stuck in a packaging machine or a tool crashing in a CNC lathe, the resulting torque spike can destroy gearboxes, twist shafts, and burn out expensive servo motors in milliseconds. A torque limiter (or overload clutch) is the mechanical fuse of the drive system. While electronic monitoring (current limiting) is common, it is often too slow to prevent physical damage from the massive kinetic energy stored in the system inertia. A mechanical torque limiter provides a physical disconnect that operates in a fraction of a second. Search for Torque Limiters & Safety Couplings Advertisement Why Choose ...
Post a Comment
Read more about this Mechanical Design Article »

Chebyshev Linkage Design: Ratios & Straight-Line Motion

Figure 1: The Chebyshev linkage converts rotary input into approximate straight-line output. Introduction to the Chebyshev Linkage The Chebyshev linkage is a four-bar mechanical linkage that converts rotational motion into approximate straight-line motion . It was invented by the 19th-century Russian mathematician Pafnuty Chebyshev , who was deeply involved in the theoretical problems of kinematic mechanisms. His goal was to improve upon existing designs, such as the Watt Straight-line Mechanism , which James Watt had used to revolutionize the steam engine. While Watt's design produces a lemniscate (figure-eight) curve with a straight section, the Chebyshev linkage is often preferred in specific machinery because the straight-line portion of the path is parallel to the line connecting the two fixed ground pivots. Search for Mechanism Design & Robotics Books Advertisement Design Ratios and Geometry The gen...
Post a Comment
Read more about this Mechanical Design Article »