Solving simultaneous equations

Solving a series of simultaneous equations is a task frequently required in engineering and scientific analysis.  Excel provides the tools to perform this task quickly and easily, but the procedure is not documented in the on-line help (so far as I can see).

The procedure is:

  • Enter the coefficients of the equations as a square matrix, that is an nxn array, where n is the number of equations, and enter the values of the equations in an n rowed column.
  • invert the matrix, using the MINVERSE() function
  • Multiply the inverted matrix by the result values column, using the MMULT() function.  The result is an array formula containing the n solutions to the equations.  The MMULT function can operate directly on the output from the MINVERSE function, as shown in the screenshot below.  Note that the results are an array formula, and which must be entered with ctrl-shift-enter.

The screenshot also shows two User Defined Functions that perform the same task:

SSOLVE() simply calls MINVERSE and MMULT.

GESOLVE() solves the equations by Gaussian Elimination, thus allowing much bigger systems of equations to be solved than can be handled by the built in functions.  I could not find a clear statement of the maximum capacity of the Excel buit-in matrix functions, but testing shows that it is something less than 160 equations in Excel 2000, and something greater than 160 in Excel 2007.

Right click to download the spreadsheet

Simultaneous.xls screenshot

Simultaneous.xls screenshot

This entry was posted in Arrays, Excel, Maths, UDFs, VBA and tagged , , , , . Bookmark the permalink.

4 Responses to Solving simultaneous equations

  1. Pingback: Links and Updates 2008-August-29 » PTS Blog

  2. Arturo says:

    Excellent file¡¡, Novel and useful file, in a single step the equations are solved with the archives UDF. Very many thanks.


  3. Pingback: Solving simultaneous equations - Fortran dll « Newton Excel Bach, not (just) an Excel Blog

  4. Pingback: Daily Download 18: Simultaneous Equations and Matrix Arithmetic Functions | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s