02 Dec 2019

mmult function

MMULT function is incredibly powerful and yet mysterious to most. Let’s explore this complex function (this post inspired by a formula challenge from contextures.com )

Duplicate Dataset Formula Challenge (contextures.com).xlsx (I added to original contextures.com file).

MMULT function

In this post I will:

MMULT Challenge

Find Duplicate Sets challenge from Contextures Nov 26 newsletter:

…how to count duplicate number sets. They had hundreds of rows, with 6 numbers in each row (yes, they were lottery results!) How many times was each number set in the list, in any order?

Rows 4 and 6 have the same numbers.

Debra’s MMULT array formula (enter with Control Shift Enter):

=SUM(IF(MMULT({1,1,1,1,1,1},TRANSPOSE(COUNTIF(B2:G2,\$B\$2:\$G\$12)))=6,1))

Robert Gascon emailed me his non array formula:

=SUM(–(MMULT(COUNTIF(\$B2:\$G2,\$B\$2:\$G\$12),{1;1;1;1;1;1})=6))

Both are brilliant compact formulas but are challenging to understand and explain for most people.

Robert challenged me to create a non array solution. I created two solutions but both have >1 step.

MMULT is a magical function! What’s the problem?

A) Explaining how it works

I create solutions that people need to use and modify. MMULT is a complex function that is challenging to explain. Even if I can explain it well, many Excel users will forget how it works and struggle to modify it.

B) Time to solve

If I’m helping someone that only has a few minutes then I can often find a quicker way to solve it. Probably not as efficient and compact as MMULT but there’s not always time to create the optimal formula. Sometimes practicality beats perfection.

How would I solve it? I have 2 solutions. Step by step and fancy textjoin.

My step by step solution:

1. 6 helper columns to extract numbers in order =SMALL(\$B4:\$G4,1)
2. concatenate the numbers =TEXTJOIN(“,”,TRUE,\$H4:\$M4)
3. simple countif =COUNTIF(\$N\$4:\$N\$14,\$N4)

This solution splits the complexity into smaller bits but there are many more steps required.

My fancy textjoin solution:

1. sort and join =TEXTJOIN(“,”,TRUE,SMALL(\$B4:\$G4,{1,2,3,4,5,6}))
2. count them =COUNTIF(\$H\$4:\$H\$14,\$H4)

This solution requires careful explanation. The SMALL(\$B4:\$G4,{1,2,3,4,5,6}) part sorts the numbers! The {1,2,3,4,5,6} is an array constant (SMALL normally only gets 1 value but we want 6). Another reason to explore alternative solutions was that Debra & Robert already hit grand slams with their MMULT formulas! All the steps I’ve done above are neatly included in their shorter single MMULT based formula.

Explaining MMULT

Most explanations start with  “it’s matrix multiplication like algebra”  …and the audience is lost. It helps to see what happens cell by cell.  Sheet ‘MMULT explained’ has a detailed explanation.

Let’s change values of array 1 to see how it affects the MMULT results (green area).

Array 1 is all zeros.

Array 2 has unique numbers.

MMULT output is all zeros.

Change cell E3 to 1.

MMULT top row = array 2 top row.

Cells E3 & F3 = 1.

MMULT top row = sum of array 2 top 2 rows.

Cells E3 & F3 & G3 all = 1.

MMULT top row = sum of array 2 top 3 rows.

The 24 in cell N10 = 1 X 7  +  1 X 8  +  1 X 9

The 33 in cell O10 = 1 X 10 + 1 X 11 + 1 X 12

etc. ….and you can do the same with cells E4, F4, G4.

Check the boxes to reveal step by step explanation:

Instead of an hour of reading I prefer to learn the basics and then play around. I can always go back later and read further details.

You’ll hopefully now be more comfortable with how MMULT works. Granted, the MMULT solutions by Debra and Robert are more complex (and brilliant!) but we’ve made progress. Audit their formulas using F9 key.

Debra Dalgleish (Contextures)

Debra is the owner of Contextures.com Millions of us have learned Excel by reading her Excel blog and her books. Debra was one of the first Excel MVPs. I also enjoy her Excel newsletter. There’s always something to learn. Debra lives west of Toronto and I live north east of Toronto.

Robert H. Gascon

Robert is a Certified Public Accountant from Quezon City, Philippines. Robert is a valued contributor of the Microsoft tech community. See his profile. He believes in building super efficient non volatile formula solutions. I’ve learned a lot from Robert this year. Besides data, Robert and I are both chess nerds.

My name is Kevin Lehrbass. I’m a Data Analyst.

I live in Markham Ontario Cnada.

Learning Excel does require a lot of hard work but it’s also intriguing, challenging and fun 🙂

1. Robert H. Gascon says:

Hi Kevin,
You had a superb explanation of MMULT. I admire how you devised 2 alternative non-array solutions. Nonetheless, I advise your readers to enter the digits from 1 to 6 in the E3 to G4 so that they will identify the specific parts of each array that are included in each part of the result. By the way, I earned the rank of Trusted Contributor at Microsoft Tech Community last October 11, 2019.
Cheers,
Robert

2. Oyekunle SOPEJU says:

Hello Kevin,
i guess I’m late to the party, right ?
Nevertheless, following your “My step by step solution” , i translated it into PQ.
See the code below:
let
Source = Excel.CurrentWorkbook(){[Name=”tblNums342″]}[Content],
BufferedTable = Table.Buffer( Source ),
Rec2List = Table.AddColumn(BufferedTable, “Step 1”, each Record.ToList( _ )),
CountIf = Table.AddColumn( Rec2List, “CountIf”, (r) => Table.RowCount( Table.SelectRows(
BufferedTable , each List.ContainsAll( r[Step 1] , Record.ToList( _ ) ) ) ) ),
#”RemoveStep 1″ = Table.RemoveColumns(CountIf,{“Step 1″})
in
#”RemoveStep 1”