How to solve simultaneous linear equations in Excel using Solver

In this blog post, I will show you how to solve simultaneous linear equations in Excel using solver add-in. You can also use Excel Matrix functions (MMULT, MINVERSE) to solve linear equations. That is really easy. But in this article, we shall focus only on the Solver method.

Let’s start.

This article is part of my series: Excel Solver – A Step by Step Complete How-to-use Guide.

We’re going to explain how to solve a set of three linear equations with three variables.

Here’s an example of a set of three linear equations:

4x+ y– 2z= 0
2x– 3y+ 3z= 9
–6x– 2y+ z= 0

We shall let Solver answer this question: What are the values of x, y, and z that satisfy all three equations?

The following figure shows a workbook set up to solve this problem. To make the formulas more readable, we have given names to three cells in this workbook:

• x: C11
• y: C12
• z: C13

All the three named cells are initialized to 1.0 (which certainly aren’t the solutions of the equations).

The solver will attempt to solve this series of linear equations.

The three equations are represented by formulas in the cell range B6:B8:

• B6: =(4*x)+(y)-(2*z)
• B7: =(2*x)-(3*y)+(3*z)
• B8: =-(6*x)-(2*y)+(z)

B6, B7 & B8 are holding these formulas.

These formulas use x, y, and z named cells’ values. The desired result for these three formulas is contained in the cell range C6:C8.

The solver will adjust the values in x, y, and z— that is, the changing cells in C11:C13 — subject to these constraints:

B6=C6
B7=C7
B8=C8

Read More: What is Solver in Excel

Solving Simultaneous Linear Equation Step by Step

Now use the following steps to set up the Solver Parameters dialog box. This dialog box appears when you choose Data ➪ Analysis ➪ Solver.

Step 1:

Fill Set Objective field with this value: $B$6.

Step 2:

Select the radio button of the Max option in To control.

Step 3:

Select cell $C$11 to $C$13 to fill the field By Changing Variable Cells. This field will show then $C$11: $C$13.

Step 4:

Add constraints one by one. The constraints are $B$6=$C$6, $B$7=$C$7, and $B$8=$C$8. These constraints will be shown in the Subject to the Constraints field. Click this article to know more about adding constraints.

Step by step process done in the Solver Parameters dialog box.

Step 5:

Deselect the Make Unconstrained Variables Non-Negative check box.

Step 6:

Select Simplex LP from the Select a Solving Method drop-down list.

Step 7:

Now click the Solve button. You will find the Solver Results dialog box shown in the following figure. Click OK, you are done with your result.

‘Solver Results’ dialog box.

The following figure shows the solution. The x(0.75), y(–2.0), and z(0.5) values satisfy all three equations.

Solver solved the simultaneous equations.

Happy Excelling 🙂

Read More…

• Solving Transportation or Distribution Problems using Excel Solver
• Minimizing shipping costs in Excel using Solver

Download Working File

Download the working file from the link below: