Matrix operations in Excel

Back in the day, when we still used an abacus to double check our results in Excel, there was a cool feature well hidden from every day use: the matrix calculation. It still exists. You'd activate it by entering the formula in a cell with Control+Shift+Enter, and it would put {curly braces} around the formula to indicate that it operated in "matrix mode."

Here's the classic example. A table with price per unit and units sold. For total revenue we need to multiply the price on each row with the number of units, and then add the whole thing together.

Either you add an extra column with the multiplication to do that (not a bad iea to see the revenue per item in this case) but that's not always wanted, or there isn't even room for such a calculation. Matrix to the rescue!

The matrix operation is Take the four cells in B3:B6 and multiply them individually with the four cells in C3:C6 and then continue the calculation with those four values. You had to enter this as a matrix operation or Excel would choke on it.

The cool thing is... not anymore. In modern day Excel you can actually perform arithmetic on groups of cells, and Excel will process those calculations first and then work with the results. That's pretty cool!

For this particular example it would make more sense to use the SUMPRODUCT function of course, because that's what it's made for. And your coworker, or Future You, doesn't have to guess what that cryptic B3:B6*C3:C6 means or how it works. It's always better when your formula clearly communicates its intention, and nothing serves that purpose better than a function that does exactly what you want to achieve.

But... the ability to use matrix operations inside regular formulas opens up a whole universe of possibilities. More to follow on that!