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

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.

Search for Numerical Methods for Engineers Books

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:

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.

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 X₁, X₂, and X₃ 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

Search for Excel VBA for Engineers Books

Next Part

Continue to the advanced application in the next post:
Solving System of Equations using Gauss Elimination Method (Part 2)

References

• Wikipedia: Gaussian Elimination
• Advanced Engineering Mathematics Resources