MMULT again - useful!

posted Jun 23, 2013, 3:08 PM by Krisztina Szabó   [ updated Mar 28, 2014, 7:13 AM ]
[...] Dr. Frederick Frankenstein:Damn your eyes.
Igor:  [to camera] Too late.
[...]
by The FrankensTeam

While we were reading Excel Formulas to Summarise Monthly Data into Quarters by Mynda Treacy, we thought a different solution. The quarterly summarization could also be done using an array formula which results a matrix.

For horizontal data the formula is:
=MMULT(--TRANSPOSE(INT((MONTH($A$2:$A$15)-1)/3)=COLUMN(A:D)-1),$B$2:$F$15)
Array-enter it to a range with 4 rows and as many columns as you have in the data table.


The advantages of this formula is the fastness when you have lot of rows in the table. It does not need any additional defined names to detrmine the quarters and it is not volatile (as OFFSET is). Also it does not require the data to be ordered by month, and you can use it for more rows, not only for 12 months (MONTH formula will read the correct month from any date.)
If you understand how the formula works, it will be easy to adapt it to summarize for example daily data to months.

How does it work?

The first step is to determine which month is in which quarter. This array formula will answer the question:
=INT((MONTH($A$2:$A$15)-1)/3)=(COLUMN(A:D)-1)

It results a matrix consists of 4 columns (one for each quarter) and as many rows as in the data table. Each row of the matrix has only one TRUE value for the quarter the month belongs to:


Now we will multiply this matrix and the data table using MMULT formula, but before doing this, we need to change TRUE-FALSE to 1-0 values (the double negation: -- will do this) and need to TRANSPOSE this matrix to have correctly dimensioned arguments for MMULT.

If your data table is arranged vertically, the formula and the logic is very similar:
=MMULT(B2:Y6,--TRANSPOSE(INT((MONTH(B1:Y1)-1)/3)=ROW($A1:$Z4)-1))

You can see both solutions in the example file.

Ĉ
roberto mensa,
Jun 23, 2013, 3:08 PM
Comments