MMULT Function (Functions in Excel)

What Does It Do ?

This function multiplies one range of values with another range of values.

The ranges do not have to be of equal size.

The dimensions of the result range is in direct proportion to dimensions of the two input ranges.

It is an Array function and must be entered using the Ctrl+Shift+Enter combination.

Syntax

=MMULT(Range1,Range2)

Formatting

No special formatting is needed.

Example

The following tables were used by a company producing boxes of chocolates.

The types of chocolate produced were Milk, Dark and White.

The company boxed the chocolates in three differing mixtures of Milk, Dark and White.

In the run up to Christmas customers ordered various quantities of each box.

The chocolate company now needed to know what quantity of each type of chocolate to produce.

The =MMULT() function was used to multiply the contents of boxes by the customer orders.

The result of the =MMULT() is the total number of each type of chocolate to produce.

	Chocolates in the box
Size	Milk	Dark	White
Giant	50	50	50
Standard	30	20	10
Economy	20	5	5
	Customers Orders
	Giant	Standard	Economy
	300	400	500
	Quantity To Produce
	Milk	Dark	White
	37,000	25,500	21,500
	{=MMULT(C32:E32,C26:E28)}
	In all three cells

How It Was Done

Cells C36 to E36 were selected.

The formula =MMULT(C32:E32,C26:E28) was typed, (but not yet entered).

The keys Ctrl+Shift+Enter were pressed to confirm the entry as an array.

The formula then showed the correct result.

Getting The Dimensions Correct

The dimensions of the Result range are directly related to the two input ranges.

The number of rows in the Result should be equal to the rows in Range1.

The number of columns in the Result should be equal to the columns in Range2.

Example 2

The following tables were used by the chocolate company to calculate the amount of

ingredients needed to produce batches of chocolate.

The company has four factories, each of which has to order enough Butter, Eggs and Sugar

to ensure they can meet production targets.

Range 1 contains the planned production of Milk and Dark chocolate for each factory.

Range 2 contains the amount Butter, Eggs and Sugar needed to make 1 unit of Milk or Plain.

The Result range shows the quantities of each ingredient that will have to be ordered to

meet the production target.

Note the depth of the Result is the same as the depth of Range 1, and the width of

the Result is the same as the width of Range 2.

	Range 1				Range 2
Production	Milk	Dark		Ingredients	Butter	Eggs	Sugar
Factory 1	20	0		Milk	1	3	10
Factory 2	20	1		Dark	2	2	5
Factory 3	10	5
Factory 4	20	10

		Result
	Ingredients To Order	Butter	Eggs	Sugar
	Factory 1	20	60	200
	Factory 2	22	62	205
	Factory 3	20	40	125
	Factory 4	40	80	250
		{=MMULT(C69:D72,G69:I70)}
			In all cells

Hint

To get a feel for how the =MMULT() function operates, set all values in Range1 and Range2

to zero 0, then change a single value in each.

Related Posts by Categories