This guide will explain how to solve a system of equations in Excel using the MMULT function and MINVERSE function.
The rules for using the MMULT function in Excel are the following:
- The
MMULTfunction returns an array containing the same number of rows selected in array1 argument and the same number of columns selected in array2 argument. - When the array1 columns do not equal or match the array2 rows, the function will return a #VALUE! error.
Since it has several built-in mathematical functions, Excel is an excellent tool for complicated calculations. In this case, we will focus on solving a system of equations.
So a system of equations is a set of two or more equations that contain the same set of unknown variables such as x, y, and z. And we need to find the values of the unknown variables to solve the system of equations.
Although this can be difficult to perform manually, Excel simplifies this task using the built-in mathematical functions. Specifically, we will utilize the MMULT function and the MINVERS function to create a formula that will solve a system of equations in Excel.
Let’s take an example wherein we need to solve a system of equations in Excel.
Suppose you have a list of linear equations containing the same number of unknown variables. So this is a system of equations that you need to solve. To make it easier, you want to use Excel to solve the equations and identify the unknown variables.
To solve the system of equations, you used the MMULT function and MINVERSE function to create a formula.
Before we learn how to solve a system of equations, let’s first understand the functions we will use.
The Anatomy of the MMULT Function
The syntax or the way we write the MMULT function is as follows:
=MMULT(array1, array2)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- MMULT() refers to our
MMULTfunction. And this function is used to return the matrix product of two arrays–an array with the same number of rows as array 1 and columns as array2. - array1 is a required argument. So this refers to the first array of numbers we want to multiply. Furthermore, it must have the same number of columns as array2 has rows.
- array2 is another required argument. And this refers to the second array of numbers we want to multiply. Similarly, it must have the same number of rows as array1 has columns.
The Anatomy of the MINVERSE Function
The syntax or the way we write the MINVERSE function is as follows:
=MINVERSE(array)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- MINVERSE() is our
MINVERSEfunction. The function returns the inverse matrix for the matrix stored or inputted in an array. - array is the only required argument. So this refers to the numeric array with an equal number of rows and columns. And this can either be a cell range or an array constant.
Great! Now we can dive into a real example of solving a system of equations in Excel.
A Real Example of Solving a System of Equations in Excel
Let’s say we have a data set containing a list of linear equations. And we want to find the unknown variables in each linear equation. So our final data set would look like this:
Moreover, the linear equations will not be inputted in the same format. However, we must input the equations in matrix notation or array form. For example, our linear equations in the system of equations are 3x + 2y + 4z = 50, 5x – 2y = 14, and 4x + 7y + 2z = 56.
Instead of inputting the equations in those formats, we will input them as an array. For example, we will input 3, 2, 4, 50 to represent the first linear equation. To solve our system of equations, we will be utilizing the MMULT function and the MINVERSE function.
So the MMULT function is used to perform matrix multiplication. Additionally, the column and row count are extremely important in the function. So the column count of array1 must be equal to the row count of array2.
For instance, we can multiply a 4 x 6 array by a 6 x 4 array to get a 4 x 4 array result. Next, the MINVERSE function is used to return the inverse matrix. So we can create a formula using these two functions to solve a system of equations in Excel.
Firstly, the MINVERSE function will be used to return the inverse matrix of our system of equations. Then, the MMULT function will be used to multiply the values and return the product of the matrix. Lastly, this will give us the values for the unknown variables.
Additionally, this formula can be used for multiple numbers of unknown variables in a system of equations. In our data set, we have three unknown variables. But, we can use the same method for a system of equations having two or four unknown variables.
So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now let’s discuss the steps of how to solve a system of equations in Excel using the MMULT function and the MINVERSE function.
How to Solve a System of Equations in Excel
In this section, we will explain the step-by-step process of how to solve a system of equations in Excel using two functions. Furthermore, each step has detailed instructions and pictures to guide you through the process.
To apply this method in your work, simply follow the steps below.
1. Firstly, we need to input our linear equations properly in a matrix or array format. In this case, we have three unknown variables to find. So we will simply type in the formula “=MMULT(MINVERSE(C2:E4),F2:F4)” in cell H2. Lastly, we will press the Enter key to return the results.
2. And tada! We have successfully solved a system of equations in Excel. Based on the results, the value for x is 0.88, y is 4.8, and the value of z is 9.44.
3. Similarly, we can perform the same method for other systems of equations having a different number of unknown variables. For example, we have a system of equations having two unknown variables.
Firstly, we can input the equations in an array format. Then, we will type in the same formula “=MMULT(MINVERSE(C7:D8),E7:E8)”. Lastly, we will press the Enter key to return the results.
4. And tada! We still successfully solved the system of equations. Based on the result, the x value is 16 and the y value is -4.
And that’s pretty much it! We have successfully explained how to solve a system of equations in Excel using the MMULT function and the MINVERSE function. Now you can apply this method to your work no matter how many unknown variables your system of equations has.
Are you interested in learning more about what Excel can do? You can now use the MMULT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
