Description
Use this function to calculate the matrix product of two arrays. The MMULT function returns the product of two arrays as a matrix.
Note: MMULT requires the number of columns in the first array to equal the number of rows in the second array.
Syntax
MMULT(array1, array2)
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
array1 |
Yes | The first array (or range) to multiply. | A numeric array or range with dimensions compatible with array2. |
array2 |
Yes | The second array (or range) to multiply. | A numeric array or range where the number of rows equals the number of columns in array1. |
Examples
Sample data
Data set 1
| A | B | C | |
|---|---|---|---|
| 1 | Resource | Hours | Rate |
| 2 | Amina | 3 | $20 |
| 3 | Raoul | 2 | $25 |
| 4 | Denise | 5 | $15 |
Data set 2
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Quantity | Component | Laptops | Tablets | Desktop | Totals | ||
| 2 | Laptops | 10 | Screens | 1 | 1 | 2 | 65 | ||
| 3 | Tablets | 15 | Batteries | 2 | 1 | 0 | 35 | ||
| 4 | Desktop | 20 | Processors | 1 | 1 | 1 | 45 |
Data Set 3
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Homework | Quizzes | Exams | Weight Avg. |
| 2 | Student 1 | 80 | 80 | 85 | 82.5 |
| 3 | Student 2 | 75 | 85 | 90 | 85.5 |
| 4 | Student 3 | 90 | 80 | 85 | 84.5 |
| 5 | |||||
| 6 | Weights | 0.2 | 0.3 | 0.5 |
Sample formulas
| Use case | Formula | Description | |||
|---|---|---|---|---|---|
| Calculate the total cost for 3 people who work different durations at different rates. | =MMULT(TRANSPOSE(B2:B4), C2:C4) |
This formula applies TRANSPOSE to (B2:B4), then multiplies that by (C2:C4). Using data set 1, this produces the following:
|
|||
| Calculate the total number of each component needed for current inventory. | =MMULT(E2:G4,B2:B4) |
This formula multiplies the "Quantity" matrix (B2:B4) by the "Component" matrix (E2:G4). This returns the total quantity of each component needed, considering quantities of each item and the number of components per item. Using data set 2, this produces the following:
|
|||
| Return a defined response if there is an error in the function. | =IFERROR(MMULT(E2:G4,B2:B4), "Invalid") |
This formula wraps the previous formula in an IFERROR to handle potential dimension mismatches. In data set 2, if one of the values was blank, this produces the following: "Invalid" | |||
| Calculate a weighted average for a set of values. | =MMULT(B2:D4,TRANSPOSE(B6:D6)) |
This formula applies TRANSPOSE to (B6:D6) then multiplies the matrix of student scores (B2:D4) by the transposed matrix of weights, producing a 3x1 matrix where each cell represents a student's weighted average grade. In data set 3, this produces the results shown in column E. Why MMULT is useful here:
|
Notes
- As with all array functions, MMULT dynamically populates results across adjacent cells, starting from the cell containing the MMULT function.
- MMULT:
- Performs matrix multiplication, a key operation in linear algebra.
- The size of the horizontal array must match the size of the vertical arrays (that is, the number of rows must equal the number of columns).
Tips
- If you have header row(s), start your array values one row down (for example in the data sets above, start with A2 rather than A1) if you don't want the headers to be included.
- MMULT can be combined with other functions, including:
Related functions
Here are the other array formula functions supported in Workiva spreadsheets.