Google Spreadsheet MMULT made easy

Google Spreadsheet provides many formulas to make our life easy. One such formula is matrix multiply or MMULT. This formula provides great possibilities to otherwise lengthy drag and drop method of implementation.

To start with I assume that you have a prior knowledge of matrices and matrix multiplication. If not try going through some brief tutorials on the internet on wikipedia or a fairly simple one here.

Now to demonstrate the usefulness of MMULT function, I will provide you an example :
Suppose you have a list of data in a column and you want to know the count of occurrence of each in that set. Also you would like to sum the values for each of them.

Say your data is in Column A from A2 till the end. Now in Column B I will take the unique values of this data :
B2 := UNIQUE(A2:A)

A normal drag and drop count could be doing the below :
=counta(iferror(FILTER($A$2:$A;$A$2:$A=$B2)))

Which would look something like this :
Using GDocs COUNTA function to count occurrence

Problem :
Using this formula we have to drag till the end and it will have some trailing zeros as there is no data. Since this is a cumbersome process,this is not so handy to use as your data grows more and more.

Solution!

The solution should be such that we don’t need to drag and the new data just pops accommodates without any changes.
Before proceeding further I would like you to understand the logic behind it.

Since we are going to get a count of occurrences, our first objective should be to get the data in proper matrix form. Which should be something like :

matrix multiplication

This means we need to first prepare two types of matrices :
1) Row matrix : We need to get a row matrix with a data as a flag (values 1,0) which should relate to the corresponding value whose count is being calculated

2) Column matrix : This matrix will is the unique set of values whose occurrence needs to be calculated.

So considering the above example for value 100 we should have a matrix something like this :

In order to get the row matrix we use few functions wrapped in ARRAYFORMULA (I will cover it in details in another post).
The formula looks like :
=ARRAYFORMULA(sign(B2:B=TRANSPOSE(A2:A)))

The above formula will give you a row matrix; Why ???
Its simple, first it will check whether the value in B2 is equal to transpose of the input data (A2:A); if yes, it will return TRUE. Now I don’t want TRUE as a value, but a 1 hence I wrap it around a SIGN function (You can also use (B2:B=TRANSPOSE(A2:A))*1 which will do the same). Now we get 1’s for the places where we have the same value as B2 and 0 otherwise. Wrapping it in arrayformula makes sure that this is performed for the full array B2 till column B end. Hence we have a row matrix for our unique values.

The other task now is to get a 1,0 value column matrix for our unique values. This can be achieved by using a formula :
= sign(row(B2:B))

Here I use the ROW function which gives the number of rows with data and SIGN gives a 1 or 0 value.

Now we construct the full matrix multiplication :

=arrayformula(MMULT(sign(B2:B=TRANSPOSE(A2:A));sign(row(B2:B))))

But if you notice, this gives some extra values for the blank cells. But we don’t want the blanks to be counted, hence we use an extra check for blank values. If they are blank we use IFERROR (I have discussed details here)

=arrayformula(if(isblank(B2:B);iferror(1/0);MMULT(sign(B2:B=TRANSPOSE(A2:A));sign(row(B2:B)))))

This brings to our results, woillaaa!!!

arrayformula in google docs spreadsheet

Now if I can add some spice to it, by getting the sum of all the values :

=arrayformula(if(isblank(B2:B);iferror(1/0);mmult(sign(B2:B=TRANSPOSE(A2:A));A2:A)))

Notice how I used the column matrix as A2:A. This is because I need the actual data to be summed up and not just the value “1” for count. Thats it!!

You can have a look with this working formula in my spreadsheet here

Due credit goes to Ahab at Google Docs forum for sharing so many great things!

Hope this helps you to start exploring Google Docs features even further!

-Abhi