Matrix operations in Excel
This is elementary, but now that I am using it so often, I thought I’d note it down.
| Function | Description |
|---|---|
| MMULT | Matrix multiplication.1 |
| MDETERM | Matrix determinant.2 |
| MINVERSE | Matrix inversion.3 |
| TRANSPOSE | Transpose of a matrix. |
For all these to work, preselect a product (or a result) area4, enter the formula in its top-left cell, hold Ctrl and Shift, and press Enter.
Update: If you take a large matrix, say a 20×20, and invert it in both Mathcad (I’m using Ver 13.0 currently) and in Excel (I’m using 2000), it is interesting to note that they do not give you apple to apple results.5 I am really tempted to put this test through a third software like Matlab. But since I don’t have a license, I’ll take its opensource clones: Scilab and Octave. Let’s see where this goes next week.
Update 2: I took my 20×20 matrix, and processed it through Scilab. Interesting results—they are almost a replication of those produced by Excel.
Update 3: Now, before I begin to become suspicious of Mathcad, I’m going to attempt the matrix to run through Matlab—through some external help.
Update 4 [Sep 1]: A colleague helped me run the matrix on his Matlab license and I finally have a benchmark to compare the results with. Matlab is consistent in its results, and—get this—matches apple to apple with Excel’s results.
I have been a fan of Mathcad for long, but this number inaccuracy is terribly uncool. I find it inexcusable for any mathematical software to compromise on accuracy—however insignificant it may be, particularly in times of double precision.
- For multiplication, number of columns in Matrix 1 should be same as the number of rows in matrix 2. [←]
- Determinants are defined only for square matrices. [←]
- A matrix has an inverse if and only if it is a square matrix, which has a non-zero determinant. [←]
- Product or result area should have the exact number of rows and columns the resulting matrix would be. For results of large matrices, I pre-border the result area, which makes selection of result area on the worksheet easy. [←]
- Although the difference is insignificant in terms of percentage. [←]
excel, math, mathcad, matlab, octave, scilab