<--- Return to Excel Statistics Guide

Solve Linear Equations in Excel using Matrix Inverse and Multiplication

Keith Greiner

Updated August 9, 2020

This essay is about finding the solution to a collection of linear equations, in Excel, using an easy two-step process. Step 1 is to find the inverse of a square matrix of x_n coefficients. Step 2 is to multiply results of Step 1 by the vector of Y values. After explaining the two step process, I show that the Excel code statements can be combined into a single step. This essay is one of three on the subject of finding solutions to sets of equations. The other two are at: this link and this link.

First, lets take a brief review of some simple linear equations and their solutions.

Everyone who has taken high school algebra should be able to solve the following formula for m, given that you know the values of x and y.

y = mx

You simply divide both sides of the equation by x, which effectively moves the x from the right to the left, under the y.

y/x = m

Then, to follow convention, you simply turn it around so the unknown is on the left.

m = y/x

Solving for x, we have the following.

x = y/m

That’s easy enough, but what if there is more than 1 value of m? What if there is a collection of m’s as in, m₁, m₂, m₃ … m_n?

What if the equation looks like the following?

y = m₁x₁ + m₂x₂ + m₃x₃ + m₄x₄ ... m_nx_n

This essay discusses the solution to multi-variable linear problems by discussing what I’ll call the Inverse-Multiplication, or “IM” method. There is a discussion in the context of an Excel spreadsheet. The problem will be solved in two simple steps with an Excel spreadsheet. At the end of the essay, the two steps will be merged into one simple Excel code statement.

Lets say we have the following system of four equations. There are five unknowns, of x₁ through x₅. If we know the correct values for x, we multiply each x value by its corresponding coefficient of m₁, m₂, m₃, m₄, or m₅, and the result will be that shown for y. The purpose of this analysis is to find a set of m₁…₅ values which could be used to find the four y values shown.

Prepare the matrix by removing the "x" text items from the matrix table, with the result shown below. The focus of calculations will be on only the m coefficient values.

Here, the blue area represents a matrix of the m coefficients, and the tan are represents the y values.

To solve for the needed m values, we simply follow the following steps.

Step 1. Find the inverse of the matrix using =MINVERSE(C21:G25). To accomplish Step 1, use the =MINVERSE(…) function and follow these steps. Microsoft describes this as an “Array Function” because it returns an array of cells. It requires a few extra steps to implement. Here are those steps.

1. Find an area below the source matrix (above) where the output will be placed. The area needs to be n x n where n is the number of unknown x values. In the example, this is four, so the area must have a size of 5 x 5.
2. In the top left cell of the destination matrix, type this Excel code. =MINVERSE(C21:G25) . You may see a result, but this is incomplete. You need a 5 x 5 result.
3. Now, use your cursor to highlight a 5 x 5 area where the cell with the Excel code is in the top left. Be sure the top left of the highlighted area is cell C34, the location of the top left cell of the results.
4. Keeping the area highlighted, go click on the Excel code text as you see it in the formula bar: usually just above the spreadsheet area.
5. Keeping the area highlighted, and having clicked in the formula bar, enter following.
   1. On an Apple computer hold down shift+command+enter.
   2. On a Windows computer, hold down shift+control+enter.
6. You should see the highlighted area become populated with values of the "inverse" matrix.

For the example data, your intermediate (inverse) matrix should have the following values.

Step 2. Multiply the inverse by the vector of y values. Like the inverse, this is what Microsoft calls an “Array Function”. This time, it returns a single column of five values that will be the m₁ ... m₅ coefficients for x₁ … x₅. Here are the steps for this function. I will plan to put the output matrix in the range form D47:D51, which is below row 38 (for this example).

1. I’m choosing range D47:D51. This is a single column range with five rows.
2. In cell D47, type the following: =MMULT(C34:G38, H21:H25). Press enter to make sure the Excel code is entered.
3. Now, with your mouse, highlight four cells in the range D47:D51.
4. Keeping the area highlighted, and having clicked in the formula bar, enter following.
   1. On an Apple computer hold down shift+command+enter.
   2. On a Windows computer, hold down shift+control+enter.
5. You should see the highlighted area become populated with m values.

The five cells D47:D51 should now contain the results of your second step. These are the m₁...m₅ coefficients.

You can verify the coefficients by multiplying each coefficient by its corresponding x value. I did this by arranging the coefficients in a row beginning in cell C59, and building a matrix of multiplied values. Once the table is constructed, sum across the rows and you should have the original y values in column H. The Excel code is shown in range B68:H73. Here you can see that row 68 points to the coefficients obtained in Excel Image 4. The other calculations, where the Excel code is shown in rows 69 through 73, show the multiplication of the results of the m₁...m₅ coefficients in row 59, by the appropriate cells in the original matrix in the range C21:G25. Column H shows row sums that are equal to the original Y values provided in range H8:12 and repeated in H21:H25. With everything matching, we have shown that the use of the =MINVERSE(...) function followed by the =MMULT(...) provides the correct m₁...m₅ coefficients that correspond to x₁...x₅ in the original set of equations.

Summary of Step 1 and Step 2.

And so in summary, this process used two steps

1. The =MINVERSE(C21:G25) function was used to find the inverse of the blue matrix of coefficients.
2. The =MMULT(C34:G38, H21:H25) function was used to find the array of xn coefficients that provide the solution.
3. Be sure to specify where the output range will be for the results of the =MMULT(...) function. In this example, it is D47:D51.

The two functions could be combined to accomplish the task in one step by using the following.

=MMULT(MINVERSE(C21:G25), H21:H25)

Again, be sure to specify where the output range will be for the results of the =MMULT(...) function. In this example, it is D47:D51.

You will, of course, want to substitute the actual cell ranges of your data. If you are using a 5 x 5 matrix, try putting your data into the cell ranges shown and discussed here, and you should be successful. If not, you will have an opportunity to practice your debugging skills.

Remember, that the functions are what Microsoft calls "Array Functions" and need to be entered in a multi-step process. You can learn more about array functions at this link.

Postscript: Why do the examples have yellow borders instead of Microsoft Excel's grey or blue?

No doubt you have noticed that the borders of the example spreadsheets are yellow instead of the standard Microsoft grey. That is because these borders allow me to copy and paste large segments of an Excel sheet without having to do a screen print small portions for each segment and assemble multiple images into one large image. If you look at other essays on my site, you will see that I have presented extensive examples that include long selections from Excel examples. Some are in .html format and some are in .pdf format. In these examples, I want to show the entire set of Excel code and calculations. Sometimes it takes a lot of data to make the example as complete as possible. All those examples need to show the row and column of the spreadsheet, so readers can see exactly how the Excel code works. Microsoft doesn't allow users to copy and paste while including the row and column margins, so the next best thing is to use the A column and the number one row to show columns and rows. That is also why the top left corner shows as "A,1". Compare that to your Excel sheet to see the difference. I colored the borders yellow to make them stand out.

<--- Return to Excel Statistics Guide