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:

There are two arrays here 1 & 2. Both of them are 2 column wide and 5 rows high. What happens when I multiply them? each cell in each row in Array 1 gets multiplied by the corresponding cell in the Array 2. For example in the second row, 2×2=4 (in black) and 1×2=2 (in red).

array4

You might have noticed that the arrays I have used till now have the same dimension i.e. the same number of rows or columns. So, is it necessary to have the same dimensions? Well the answer is YES and NO. (I know ! that does not answer anything, so let me explain in the case below)

Case 2: when one of the arrays has just one row or one column but the other has multiple

In the example below,  Array A has just one row while array B has 5? So, how will the calculation work?

array1

Simple, the single value of Array A will get multiplied with all the values in the second array Array B. This is the only case when you would see a value from the first array getting multiplied to all the values in the second array irrespective of its row or column. And this means that even if the second array had multiple columns and multiple rows, it would still work.

Case 3:  when one of the array is in rows (R = many ; C = 1) and another in columns (R = 1 ; C = many)

So, let’s look at the table below. Array A has dimensions of R = 5 and C =1, while Array B has dimensions of R =1 and C =5.

Array3

The size of the output array, in such calculations, is defined by the maximum number of rows and columns present in the participating arrays. So, in this case,  the output array will have R = 5 ; C = 5 (Array A has max rows and Array B has max columns). The calculation is explained in the table above i.e. every row in Array A is multiplied with every Column in Array B.

Are there other multiplication possibilities?

Why not ! in the example above you can also multiply the Output array with Array A again. Any guesses what will happen in this case? …. Each row in Array A will be multiplied to all the column in corresponding row of the Output Array.

So can we also multiply Array B with the Output array? Yes, absolutely.

Case 4: not everything can be multiplied

There has to be a logical explanation in which arrays can be multiplied. So the following multiplication will not work.

For example , when we multiply the following arrays we get an error in the output array as there is no logical explanation in which these arrays can be multiplied.

wrong multiplication

It’s perfectly fine if you are a bit confused a bit right now. Things will get clearer in the posts that follow.

So, we have covered most of the basics that we need to know. In the next post I will explain how to enter array formula and how they can be used in your dashboards to solve complex problems.

Till then keep excelling and don’t forget to leave a comment – we really cherish them :)

2 thoughts on “The Mathematics of Arrays

Have a question? just leave a comment and we will answer A.S.A.P