Want to solve simultaneous linear equations in Excel? In today’s tutorial, we’ll go over some of the easiest ways to do so and how you can get the results you need quickly. Before we begin, let’s understand what simultaneous linear equations are.
Solving simultaneous linear equations
Simultaneous linear equations are a set of two or more equations, each containing two or more variables whose values can simultaneously satisfy both or all the equations in the set. For example,
4x + y – 2z = 0, 2x – 3y + 3z = 9 and -6x – 2y + z = 0
is a set of simultaneous linear equations in x, y, and z. We need to find such values of x, y, and z that satisfy all three equations simultaneously. In Excel, the solution can easily be calculated using two ways:
1. Using the MMULT and MINVERSE function
The three equations can be written in matrix form as:
or simply AX = B, where A is the coefficient matrix, X is the column matrix containing the variables x, y, and z and B is the column matrix containing elements 0, 9, and 0. We have to find matrix X. The equation can further be modified as X = A-1 B, where A-1 is the inverse of matrix A. Then, multiplying matrix A-1 with matrix B, we get matrix X and the values for x, y, and z. First, we need to calculate the inverse of matrix A. Follow these steps to get the inverse of matrix A:
- Select the cell where you want to calculate and display the first element of the inverse of matrix A.
- Type =MINVERSE(B1: D3), where cells B1: D3 contain the elements of matrix A.
- Press the Enter key if you have a current Microsoft 365 subscription else press CTRL+SHIFT+ENTER to display the resultant matrix.
Now that we have the inverse of A, we simply have to multiply it with matrix B to get matrix X. Follow these steps to calculate matrix X:
- Select the cell where you want to calculate and display the first element of matrix X.
- Type =MMULT(B6: D8,G1: G3), where cells B6: D8 contain the elements of matrix A-1 and G1:G3 contain the elements of matrix B.
- Press the Enter key if you have a current Microsoft 365 subscription else press CTRL+SHIFT+ENTER to display the resultant matrix.
We get the values of x = 0.75, y = -2, and z = 0.5 which is the solution for the following set of simultaneous linear equations: 4x + y – 2z = 0, 2x – 3y + 3z = 9 and -6x – 2y + z = 0.
2. Using Excel Solver Add-in
First, we need to add Solver add-in to the Excel ribbon. Follow these steps to access Excel Solver:
- Click on the File tab in the top left corner of the Excel window.
- From the menu that appears, click on Options at the bottom left.
- In the Excel Options dialog box, select the Add-ins option.
- Select Excel Add-ins from the Manage drop-down list and click on Go.
- In the Add-ins dialog box that appears check the Solver Add-in checkbox and click on OK.
- The Solver Add-in is now accessible from the Analysis group in the Data tab.
Follow these steps to solve for x, y, and z using Solver
- Select the cell containing the value for variable x and type x in the Name Box.
- Repeat the process to name the values for variables y and z.
- Go to cell B2 and type the formula =4*x+y-2*z
- Go to cell B3 and type the formula =2*x-3*y+3*z
- Go to cell B5 and type the formula =z-6*x-2*y
- Click on Solver in the Analysis group on the Data tab.
- In the Solver Parameters dialog box, type $B$2 in the Set Objective: text box.
- Select the radio button Value Of: and type 0 in the field as 0 is the desired value for that equation.
- In the By Changing Variable Cells: text box, type $C$7:$C$9, as cells C7:C9 contain the values of variables we want to change to satisfy the equation.
- Click on the Add button next to Subject to the Constraints: box, a dialog box appears.
- Type the constraint $B$2=$C$2 in the Add Constraint dialog box and click on OK.
- Repeat the above two steps for constraints $B$3=$C$3 and $B$4=$C$4.
- Uncheck the Make Unconstrained Variables Non-Negative check box.
- Select Simplex LP option from the Select a Solving Method: drop-down list and click on Solve.
- The Solver Results dialog box displays the message: Solver found a solution. All Constraints and optimality are satisfied. Uncheck the Return to Solver Parameters Dialog check box if you’re satisfied with the solution and click on OK.
We can now see that Excel Solver has found values of variables x, y, and z which satisfy the given set of simultaneous linear equations. This can also be verified as the values from the formulas are equal to the desired values.
Conclusion
In this tutorial, we learned how to solve a set of simultaneous linear equations both by using the inverse of a matrix and by using the Excel Solver Add-in.