Excel Array Matrix Operation

Excel Array Matrix Operation1

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

Excel Array Matrix Operation1

STEP2# In the formula bar, enter the following array formula

=MMULT(D3:F11,I3:I5)
Excel Array Matrix Operation1

STEP3# Press Ctrl+Shift+Enter shortcut keys to convert the above ordinary formula into an array formula.

{=MMULT(D3:F11,I3:I5)}
Excel Array Matrix Operation1

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.

Related Posts

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference ...

Excel Structure Reference

This tutorial introduces the basics of structured referencing in Excel and shows you how to use structured referencing in a table with examples. When you use formulas that reference tables, whether the formula references a part of the table or ...

Excel/Google Sheets: Full Row Reference

Excel or Google sheets supports both full row references and full column references. This article will talk about the usage of full row references. Full Row reference is another cell reference in Excel or Google Sheets, which is used to ...

Excel/Google Sheets: Full Column Reference

Full column reference is another cell reference in Excel or Google Sheets, which is used to reference the entire column, for example, if you want to reference the entire column A, you can use the following reference format A: A. ...

Excel/Google Sheets: Expanding Reference

An expanding reference is a reference type in Excel and Google worksheets that extends the range of a cell when a formula is copied to the cell below or to the right. In this article, we will explain how to ...

Comments

So empty here ... leave a comment!

Leave a Reply

Sidebar