How can I solve a system of linear equations in Excel?

excel math matrix linear-equation
e.James picture e.James · Feb 12, 2010 · Viewed 32.4k times · Source

I have a system of 22 linear equations (exactly 22 equations and 22 unknowns) which are dynamically generated in an Excel spreadsheet. Is there a way to have Excel solve the system and produce a value for each of the unknown variables?

Another SO question has a lot of good information about the algorithms used to solve such equations. Cramer's rule in particular looks promising, but I'm not sure how to implement it in Excel.

Any help is appreciated!

Answer

Peter picture Peter · Feb 12, 2010

This page gives the following simple method:

  1. Enter the coefficient matrix in an nxn range.

  2. Enter in the vector of constants in a n-tall column. Select a blank n-tall column. It is important that you not have only one cell selected.

  3. Type, =MMULT(MINVERSE(coefficients_matrix_range), constants_vector_range) in the formula text box and press [CTRL][SHIFT][ENTER]. It is important that you not simply press [ENTER].

This should work adequately, since you only have a small system (22x22). While, ideally, you wouldn't invert the matrix directly, it shouldn't matter in this case.

You definitely don't want Cramer's rule. Due to its fairly poor numerical properties, it's more of theoretical than practical interest.

Related questions

calculate probability and draw a cdf in Excel

Now I have a column of data like this: 0.000000 0.000000 0.000000 0.000000 0.024995 0.024996 0.024996 0.024997 0.024997 0.024997 0.024997 0.025004 0.025010 0.025011 0.025996 0.025996 0.025996 First I want to calculate the cumulative probability of these data, and show them in column B, then based on Column A and B, to draw a CDF graph. Anyone one …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More