How to Solve a System of Equations in Excel

To solve a system of equations in Excel, you can use the built-in matrix functions such as MMULT(), MINVERSE(), and TRANSPOSE(). Here's a step-by-step guide on how to do this:

1. Prepare your system of equations in the form Ax = B, where A is the matrix of coefficients, x is the column vector of unknowns, and B is the column vector of constants. For example, consider the following system of equations:

2x + 3y = 5
4x - y  = 3

2. In Excel, enter the coefficients and constants in separate cells. For instance, you can enter the coefficients in cells A1:B2 and the constants in cells C1:C2.

A1: 2  B1:  3  C1: 5
A2: 4  B2: -1  C2: 3

3. Calculate the inverse of the coefficient matrix A. To do this, select a range of empty cells with the same size as the coefficient matrix (2x2 in our example). In this case, let's use cells E1:F2.
4. With the range E1:F2 selected, type the following formula:

=MINVERSE(A1:B2)

5. Press Ctrl + Shift + Enter to create an array formula. Excel calculates the inverse of the coefficient matrix and displays the result in cells E1:F2.
6. Now, you need to multiply the inverse of the coefficient matrix (in cells E1:F2) with the column vector of constants (in cells C1:C2). Select another range of empty cells with the same number of rows as the coefficient matrix and one column. In this case, let's use cells G1:G2.
7. With the range G1:G2 selected, type the following formula:

=MMULT(E1:F2, C1:C2)

8. Press Ctrl + Shift + Enter to create an array formula. Excel calculates the product of the matrix and the vector and displays the result in cells G1:G2.
9. The resulting column vector in cells G1:G2 now contains the solution to the system of equations. In our example, G1 contains the value of x, and G2 contains the value of y.

Example

Here's an example using the system of equations mentioned earlier:

2x + 3y = 5
4x - y  = 3

1. Enter the coefficients and constants in cells A1:C2:

A1: 2  B1:  3  C1: 5
A2: 4  B2: -1  C2: 3

2. Calculate the inverse of the coefficient matrix in cells E1:F2:

E1:  0.2  F1:  0.6
E2:  0.8  F2: -0.4

3. Multiply the inverse of the coefficient matrix by the column vector of constants in cells G1:G2:

G1: 1
G2: 1

The solution to the system of equations is x = 1 and y = 1.