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:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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^{-1}B.

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))
**

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]