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)
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
Interpersonal Skills, and
Professional Behavior and calculate the final score. The Secretarial Skills, Interpersonal Skills, and Professional Behavior scores are weighted at
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
STEP2# In the formula bar, enter the following array formula
STEP3# Press Ctrl+Shift+Enter shortcut keys to convert the above ordinary formula into an array formula.
From the screenshot above, you can see that
TOM had the best interview score, his interview score was
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.