Suppose you have the following system of linear equations and want to solve for x, y, and z:
- 2x + y - z = -1
- -4x + 11y = 18
- x - y + 2z = 9
Is there an easy way to do this with Excel using worksheet functions?
This can be done by using a little linear algebra and Excel's matrix functions.
First, create an augmented matrix that represents your equations on a worksheet.
Since you want to solve for three variables, select 3 cells in a column where you want your results to be returned. Without changing the selection, type the following formula:
Press Ctrl+Shift+Enter to enter the formula in the selected array. And that will return the solution to your system of equations.
This will work for larger systems of equations with more variables as well. This formula works based on the observation that the left-hand side of a system of linear equations can be represented as the product of an n by n matrix, A, and an n by 1 matrix of variables, x, and the right-hand side can be represented as an n by 1 matrix, B. To solve this matrix equation, both sides must be multiplied by the inverse of A.
Thus, the solutions are just the matrix product of the inverse of the square matrix and the matrix of the constants on the right-hand side of the equations.