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
Pingback: Links and Updates 2008-August-29 » PTS Blog
Excellent file¡¡, Novel and useful file, in a single step the equations are solved with the archives UDF. Very many thanks.
LikeLike
Pingback: Solving simultaneous equations - Fortran dll « Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 18: Simultaneous Equations and Matrix Arithmetic Functions | Newton Excel Bach, not (just) an Excel Blog
This website is completely awesome. I’ve search these informations a
long time and I view it that is good written, fast to understand.
I congratulate you for this article that I am going to tell to the people friends.
I ask you to recommend the gpa-calculator.co page where
each pupil or learner can find ratings grade point average marks.
All good!
LikeLike