Array formulas

Excel supports a special type of formulas that allows us to use arrays of variables as arguments rather than variables (references, ranges, names), where we can perform certain mathematical operations over all of them. Moreover, there is a situation where the arrays considerably shorten the way to the solution or are even necessary to get to it. In the following text, through a few examples, it will be shown how to use array formulas.

Array formulas can be used in combination with any Excel function, and in the first example we will use the SUM function. Our task, on this occasion, is to calculate the sum of products of two sets. The same result is obtained using the SUMPRODUCT function, but here we want to get it with the help of the SUM function and two arrays as arguments. Enter the formula:

=SUM(B2:B6*C2:C6)

Now if we would press ENTER key we would receive an error message. The error occurs because the SUM function basically does not accept the product of the two arrays as an argument. Therefore, we end up entering the formulaby pressing the CTRL + SHIFT + ENTER key combination. The formula thus entered will be displayed framed by curly braces, indicating that it has arrays as arguments:

{ =SUM(B2:B6*C2:C6) }

If we want to change such formula we just have to keep in mind that at the end of editing the combination of the CTRL + SHIFT + ENTER key is pressed again to preserve formula’s integrity.

The array that we set as an argument does not have to be a reference, but it can also have a constant value. In this case, the array consists of constant  values separated by commas in the brackets, for example:

={1,2,3}

So, if we would create a formula like:

{ =SUM({1,2,3}*{1,2,3}) }

Excel would perform a sum of products of array members, ie (1*1)+(2*2)+(3*3), which as a result returns number 14. When creating array formulas it is OK to combine range references with constant values, so it’s easy to conclude that such formulas bring lots of new calculation possibilites.

Finally, let’s mention the TRANSPOSE function whose goal is to transpose array of numbers. That is, if the array is in the column function shifts is to row and vice versa. The TRANSPOSE function syntax is:

TRANSPOSE (<array>)

This function is used by first selecting the range where we want to transpose an array. If the array of values in the row is selected, the column with the same number of cells should be a destination point and vice versa. Then enter the formula, for example,

=TRANSPOSE (A1:E1)

and at the end press CTRL+SHIFT+ENTER. Result are transposed values within a selected range.