Home > Engineering > Excel Simultaneous Equation Solver

Excel Simultaneous Equation Solver

Don’t have access to MATLAB or your nuclear powered calculator handy? You can use Excel (or any other spreadsheet application) to solve your simultaneous equations quickly and easily using matrices. As an example, the following system of equations will be used:

-x + y + 2z = 10
3x + 2y - z = 12
6x + 5y + 3z = 14

Step 1: Open up excel, and place the coefficients of the variables x, y, and z in cells arranged as a matrix. Place the right hand side (RHS) values of the equations in an adjacent column as in the following image.

Step 2: Somewhere in the spreadsheet, select a region 3 cells across by 3 cells down, 9 cells total. These cells will hold our inverse of the coefficient matrix. Hit the equal sign key to initiate a formula. Type MINVERSE( and select the range of cells that holds the coefficient matrix (my spreadsheet has MINVERSE(B4:D6)). Close the parenthesis and hit CTRL, SHIFT, and Enter at the same time. This will populate the cells with the inverse values. See the image below.

Step 3:Select 3 vertical cells in the spreadsheet. These cells will hold the answer to the equations. Hit the equal sign key to initiate a formula. Type MMULT( and select the range of the matrix inverse, type a comma, then select the vertical RHS values. Close the parenthesis and hit CTRL, SHIFT, and Enter at the same time. This will populate the cells with the answer to the system of equations, which should give:

x = - 6.2
y = 13.0
z = - 4.6

Remember that if a matrix determinant is zero, the matrix is singular and the system cannot be solved. If your coefficient matrix is singular, yow will get a #NUM! error in the answer cells. Double check your coefficients.

Download the Excel spreadsheet (xlsx)

About these ads
Categories: Engineering
  1. Yen
    September 3, 2011 at 4:57 am

    Hello Jerry,

    I think there is an error in your article. It should be CTRL, SHFT and Enter not CRTL, ALT and Enter.

    Regards,
    Yen

    • September 6, 2011 at 12:13 pm

      Good eye Yen, I have updated the post

  2. November 25, 2013 at 3:30 pm

    It still says CTRL, ALT, and Enter in step 2.

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: