Tag Archives: array formulas

The Mathematics of Arrays

In the last post I explained the basic logic of array formulas and how they work. In this post I will be focusing on the mathematics of arrays – so what happens when you subtract one array from another or multiply or divide two arrays? How exactly does it work?

For the purpose of this post I will just be multiplying various arrays but you can basically do any mathematical (subtract, divide, add, etc.) or non-mathematical  (concatenation, find length, etc.) operation in the same fashion – literally anything and everything that you could think of.

Whenever I start with this concept, I get a common question – is this matrices multiplication that we learnt in school – so let me answer this, just in case – and the answer is NO. Array multiplication is a row wise multiplication. So each row of the first array gets multiplied with each row of the second array – in most of the cases.

Now, let’s look at all the possible cases of array multiplication:

Case 1: when both the arrays have the same number of rows and columns

In the image below, I multiply two arrays A & B (essentially range  C6:C10 & E6:E10). When I do this in a cell, I get the result that is displayed in the Output Array below. So, each cell in each row of the array A gets multiplied with the corresponding cell in array B.

Array2

So why did I say “each cell in each row”? To understand this let’s look at the image below: Continue reading