O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

9.4. Solving Linear Systems

Problem

You'd like to solve a linear system of equations using Excel.

Solution

You can take one of several approaches to solving linear systems in Excel. You could, for example, use VBA to program any of the standard numerical algorithms for solving systems of equations. While this approach would undoubtedly work, it's the most time-consuming since you'd have to program and test the algorithm. Since Excel provides built-in support for matrix operations, you could frame the problem in the form of a matrix problem and perform the necessary matrix inversion to find a solution. An easier way is to use Solver. In the discussion that follows, I'll show you the latter two approaches.

Discussion

If you can frame the system of equations in the form of a matrix equation, then you can use direct matrix inversion to find a solution. If you can't or don't want to do so, or if you're dealing with nonlinear equations, you can use one of Solver's several approaches.

Matrix inversion

In Recipe 7.10, I showed you how to use Excel's built-in matrix functions to multiply and invert matrices. You can use these same functions to solve a linear system of equations of the form:

The approach to solving this problem is a straightforward application of the built-in matrix functions, where you can find the unknown xs that satisfy this equation by solving the following matrix equation: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required