Solving a System of Simultaneous Linear Equations in Excel

Systems of linear equations can be solved in Excel using formulas that perform matrix calculations. This worksheet is set up to find three unknowns in a free-body diagram using some of those matrix functions.

The problem contains a beam supported by a pin and a tension cable, with a weight at the opposite end:

All of the variables are entered in the spreadsheet:

• L1, the length from the pin to where the tension cable attaches on the beam
• L2, the overall length of the beam
• θ, the angle between the tension cable and the beam
• W, the weight hanging from the end of the beam.

From the free-body diagram in the lower left, we can obtain a system of equations summing the forces in the x-direction, the forces in the y-direction, and the moments about the pin. It’s a linear system that has three equations and three unknowns, so we can solve it using linear algebra with the formula Ax=B, where A is the matrix of coefficients, X is the array of unknowns, and B is an array of constants. We can re-arrange this equation to x=A^-1B.

To start, we need to populate the matrices on the right:

The first equation represents the top row of the matrix and the first entry in the array of constants. The coefficient of F_x is zero, the coefficient of F_y is zero, and the coefficient of F_t is the negative of the cosine of the angle in radians:

=-cos(radians(I7))

The constant for the first equation is zero.

For the second equation, the sum of forces in the y-direction, F_x, is zero, F_y is 1, and F_t is the sine of the angle in radians:

=sin(radians(I7))

The constant for this equation is the weight, W, so set this cell equal to cell I8.

Finally in the third equation, the coefficients for F_x and F_y are zero, and the coefficient for F_t is:

    =L1*sin(radians(I7))

The constant for this equation is the weight times L2:

=I8*I6

The first two matrices should be:

To solve for X, the array of unknowns, you’ll first need to invert A and then multiply it by B. To do the matrix inversion, use the function MINVERSE. This will be an array formula, so first select all of nine cells in the array for A^-1. Enter into the formula bar:

    =MINVERSE(

Then select the cells in the A matrix. Type Ctrl-Shift-Enter to enter the array formula.

To find X, we’ll multiple A-1 by B using the function MMULT. This function is used for matrix multiplication. Enter the function into the formula bar, and select the matrix A^-1 for the first argument. Add a comma, then select the matrix B for the second argument. Your formula should be:

    =MMULT(L13:N15,P7:P9)

Then use Ctrl-Shift-Enter.

We could have done this in one step using nested formulas, using the MINVERSE function as an argument for MMULT. Select the three cells in the Results section, and enter the formula:

    =MMULT(MINVERSE(L7:N9),P7:P9))

This function will multiply the inverse of the A matrix by the B matrix, all in one step. Use Ctrl-Shift-Enter to enter the array formula.

Now that the spreadsheet is set up to solve this system of linear equations, you can change any of the inputs and update all three of the unknown forces simultaneously.