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.zip
Simultaneous.xls screenshot
Filed under: Arrays, Excel, Maths, UDFs, VBA | Tagged: Excel, Matrix, Simultaneous equations, UDF, VBA
[...] Solving simultaneous equations on Doug Jenkins’ Newton Excel Bach blog. The post shows built-in matrix techniques for solving simultaneous equations, and includes a couple UDF techniques as well. [...]
Excellent file¡¡, Novel and useful file, in a single step the equations are solved with the archives UDF. Very many thanks.
[...] to a Fortran simultaneous equation routine, compiled as a dll. The original file was posted in solving simultaneous equations. The advantages of a compiled routine [...]