Systems of linear equations pop up everywhere you look. Small retail shops want to maximize the profit from the distribution of goods taking up limited shelf space. Chemists need to balance constituents of a complex reaction. Economists are compelled to build models; sometimes of businesses, sometimes of entire nations. We’re going to take a look at solving linear equations using Microsoft Excel. Although Excel is designed to be a highly interactive tool for bookkeeping and analysis and is not intended to compete with dedicated mathematics tools like SciLab and Matlab, Excel has no trouble solving the fundamental problems encountered by most businesses.
If fact, the greatest difficulty encountered when solving linear equations with Excel lies not in Excel, but in formulating the problem in a structure that Excel understands. To focus on correctly formulating the problem, we will use an extremely simple pair of simultaneous equations for out illustration. This set of equations is from a discussion in the fun introductory text for mathematical economics written by Anthony and Biggs. For our problem, we’re going to take a pair of equations that define what economists call the demand set and the supply set. We seek a stable equilibrium point; in other words, the point where supply equals demand.
2q – 15p = -20
q + 5p = 40
If we plot these equations in Excel, we are not surprised to see that as we move along the horizontal axis, that is, in the direction of increasing supply, the demand set line shows the price decreasing along the vertical axis. Similarly, if we follow the supply set line we observed that increasing price along the vertical axis will be associated with a corresponding increase in the supply. We readily see that the linear plots intersect; we now need to calculate the point of intersection.
There are many ways to solve a set of simultaneous linear equations. If we wish to use Excel, the most direct way is to express our problem in matrix notation.
A set of linear equations can be expressed as a matrix times a vector equaling a vector of known values. The algebra looks like this:
For our problem, what this notation is saying is
We can find q and p by multiplying the (-20, 40) vector by the inverse of A, and that is exactly what we will have Excel do. Algebraically, what we will do is this:
x = A-1b
In Excel this is easy. We enter the matrix in any 2 X 2 array of cells:
Here we have labeled the matrix “A” but this is not necessary for our calculation. Now we’re all set to calculate the solution to our equations.
Calculate the matrix inverse by selecting another 2 X 2 array of cells. In the formula bar, start typing =MINVERSE( and then highlight the cells of the first matrix (i.e. “A”) to indicate that this array is the argument to the MINVERSE function. Close the parentheses but do not hit <enter>. MINVERSE is an example of what Mr. Excel refers to as a “CSE” function. This handy mnemonic reminds us that you must hit <ctrl><shift><enter> for the array argument to be properly entered.When you are finished, the formula bar should look like this:
Note: You cannot enter the curly braces manually for CSE functions. You must hit <ctrl><shift><enter>!
Now that we have the matrix inverse, we can apply it to the vector (-20, 40) using matrix multiplication. You guessed it – another CSE function! Enter -20 and 40 in two cells in a column. Highlight two empty cells in a neighboring column. In keeping with mathematical convention, our answers will appear as a column vector; rows won’t work! After selecting two empty cells in a column, type = MMULT( in the formula bar. Highlight the inverse 2 X 2 matrix to enter it as the first argument, add a comma, then highlight the (-20,40) vector as the second argument. Close the parenthesis, and by now you know that you will hold down the control and shift key prior to hitting <enter>. The results should look something like this:
Of course, we always want to confirm that our results are correct, so we insert q=20 and p=4 into the original equations and prove that we have found our equilibrium point. We also take pleasure in observing that this is consistent with the intersection point visualized on the chart.
Mathematical purists will note that calculating a matrix inverse is not the most efficient way to solve a system of linear equations. However, it becomes very efficient if we would like to apply the same matrix inverse to several different sets of parameters. Of course, it is just this sort of “what-if” analysis that makes Excel such a desirable tool to begin with.
If you would like to download the completed workbook, click here. Please feel free to drop me a note if you have any comments or questions.
To learn more, have a look at our full curriculum of Excel courses including 3 new 1-day online courses!