How to Correctly Use MMULT and MINVERSE Formulas in Excel
Question details
The user needs to correct a formula error when attempting to calculate the inverse of a matrix and multiply it by another matrix.
- Product
- Excel for Mac
- Device & OS
- Mac
- Scenario
- Performing advanced mathematical matrix multiplication using array formulas.
- Observed behavior
- The formula fails because the user passed two ranges into MINVERSE instead of correctly nesting MINVERSE inside the MMULT function.
Ensure that your first matrix range is a square, invertible matrix (having an equal number of rows and columns) and that the second matrix has dimensions compatible for multiplication.
Combine MMULT and MINVERSE Properly
Use MMULT to multiply the inverse matrix (generated by MINVERSE) with your second matrix to get the correct array result.
MINVERSE is designed to take exactly one square matrix. To multiply the inverted matrix by another set of data, you must wrap MINVERSE as the first argument inside the MMULT function.
Click on the cell or highlight the range where you want the resulting matrix to appear.
Type the formula =MMULT(MINVERSE(Q59:R60), T59:T60), replacing the cell references with your actual matrix ranges.
If you are using an older version of Excel without dynamic arrays, press Cmd + Shift + Enter on your Mac keyboard to evaluate it as an array formula.
Wrap with SUMPRODUCT for a Single Value Result
If your calculation needs to output a single combined value rather than an array, you can wrap the matrix functions in a SUMPRODUCT function.
Perform Advanced Matrix Calculations Easily in WPS Office
WPS Spreadsheet supports advanced array formulas, including MMULT and MINVERSE, with full compatibility for Excel formulas. You can perform complex mathematical operations effortlessly.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the file containing your matrix data.
- 2. Enter the matrix formula: Select the output cell and type =MMULT(MINVERSE(array1), array2).
- 3. Calculate the result: Press Enter to instantly process the matrix inversion and multiplication.
Frequently Asked Questions
Why is my MINVERSE formula returning a #VALUE! error?
A #VALUE! error usually occurs if the matrix contains empty cells, text instead of numbers, or if the matrix is not square (e.g., it doesn't have an equal number of rows and columns).
Do I need to press Cmd+Shift+Enter for these formulas on Mac?
In newer versions of Excel (like Excel 365 or Excel 2021) with dynamic arrays, you only need to press Enter. For older versions, you must use Cmd+Shift+Enter to execute them as array formulas.
Why does MMULT return a #NUM! error?
This error happens if the matrix to be inverted has a determinant of zero (making it singular and non-invertible). Ensure your data represents a valid invertible matrix.
