Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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:

=MMULT(MINVERSE(A2:C4),D2:D4)

Press Ctrl+Shift+Enter to enter the formula in the selected array. And that will return the solution to your system of equations.

enter image description here

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.

Ax = B
inverse(A)Ax = inverse(A)B
x = inverse(A)B

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.

share|improve this answer
    
Can be achieved with Solver too! –  pnuts Dec 18 '12 at 21:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.