Wednesday, July 5, 2017

MMULT function

MMULT function

This article describes the formula syntax and usage of the MMULT  function in Microsoft Excel.

Description

Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.

Syntax

MMULT(array1, array2)

The MMULT function syntax has the following arguments:

  • Array1, Array2    Required. The arrays you want to multiply.

Remarks

  • The number of columns in Array1 must be the same as the number of rows in Array2, and both arrays must contain only numbers.

  • Array1 and Array2 can be given as cell ranges, array constants, or references.

  • MMULT returns the #VALUE! error when:

    • Any cells are empty or contain text.

    • The number of columns in Array1 is different from the number of rows in Array2.

  • The matrix product array a of two arrays b and c is:

    Equation

    where i is the row number, and j is the column number.

  • Formulas that return arrays must be entered as array formulas.

    Note:  In Excel Online you cannot create array formulas.

Examples

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Array 1

Array 1

1

3

7

2

Array 2

Array 2

2

0

0

2

Formula

Description

Results

'=MMULT(A2:B3,A5:B6)

The results should be 2, 14, 6, and 4, in cells C8, C9, D8, and D9.

=MMULT(A2:B3,A5:B6)

=MMULT(A2:B3,A5:B6)

=MMULT(A2:B3,A5:B6)

=MMULT(A2:B3,A5:B6)

Note: To work correctly, the formula in the example needs to be entered as an array formula in the Excel program. After copying the example to a blank worksheet, select the range C8:D9 starting with the formula cell. Press F2, and then press Ctrl+Shift+Enter. If the formula is not entered as an array formula, a single result (2) will be returned in cell C8.

Example 2

Customer

Product Qty

Corks

Bottles

Barrels

Contoso, Ltd.

14

9

3

Coho Winery

2

11

15

Price

Weight (lbs)

Product

$200

4

Corks (500/box)

$250

42

Bottles (case)

$425

115

Barrel

Customer

Sales

Total weight

Contoso, Ltd.

=MMULT(B3:D4,A8:B10)

=MMULT(B3:D4,A8:B10)

=MMULT(B3:D4,A8:B10)

Coho Winery

=MMULT(B3:D4,A8:B10)

=MMULT(B3:D4,A8:B10)

=MMULT(B3:D4,A8:B10)

=MMULT(B3:D4,A8:B10)

=MMULT(B3:D4,A8:B10)

=MMULT(B3:D4,A8:B10)

Note: The formula in B13:D15 needs to be entered as an array formula to work correctly.

No comments:

Post a Comment