In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the number of rows and columns of the two arrays passed in are the same.
MMULT function is used to calculate the matrix product of two arrays, its syntax structure is as below:
=MMULT (arrayl, array2)where array1, array2 are the two arrays to be multiplied. the number of columns of array1 and the number of rows of array2 must be the same, and both arrays can only contain numeric elements.
Excel Array Matrix Operation Example
If you have an interview score sheet, you need to score Secretarial Skills, Interpersonal Skills, and Professional Behavior and calculate the final score. The Secretarial Skills, Interpersonal Skills, and Professional Behavior scores are weighted at 0.3, 0.5, and 0.2, respectively.
In order to calculate the final interview scores for each person, you need to perform a matrix product of the skill item scores and the weighted value scores by using the MMULT function, which you can refer to the following steps.
STEP 1# Select the cell area G3:G11
STEP2# In the formula bar, enter the following array formula
=MMULT(D3:F11,I3:I5)
STEP3# Press Ctrl+Shift+Enter shortcut keys to convert the above ordinary formula into an array formula.
{=MMULT(D3:F11,I3:I5)}From the screenshot above, you can see that TOM had the best interview score, his interview score was 4.7.
Since the interview results table on the left and the weight table on the right are mutually corresponding, you can use the MMULT function to calculate the matrix product of the array of scores and the array of weights to get the final interview results. The MMULT function returns an array of results, which must be entered as a multi-cell array formula in order to fill the G3:G11 cell area with the results.
