What are Array Functions or Array Formulas

Array functions are one of the powerful tools that Excel has provided. They are able to achieve many things that one cannot achieve by working with functions normally. Here is a detailed discussion about what are Array Functions.

Array: is basically a set of values. It is a popular concept in computer programming.

Usage of Array functions: In MS Excel, there aren't separate array functions as such, as it might seem from reading the term - 'Array Functions'. Rather, there are a few functions which can be used as array functions.

As we know that arrays are 'a set of values', the excel functions which are used on ranges, can also be used as array functions. These functions, when used as array functions, treat the range as arrays and process them accordingly.

A simple example:

Open up a worksheet and work with this example to understand the concept.

Now do this -

1. In the cells A1 to A10 write the numbers from 1 to 10
2. Similarly in cells B1 to B10, write the same numbers
   
   Now suppose, you want to multiply each cell in column A with the corresponding cell in column B and then sum it. One of the ways is to use the Sumproduct() function (I will discuss this function in later posts). And the other way is to first use column C to calculate the products of the cells and then sum them in column C.

We will not discuss here why not to use Sumproduct() as it is a better option for such kind of problems. But array functions can achieve what Sumproduct() cannot. Just to explain the concept of array functions, let us try to achieve sum of products using array functions.

In the second approach note that each cell of the range (cells 1 to 10) are processed individually. This is the essence based on which the array functions are based, i.e., whenever you want to process each cell in range individually but in a batch, then array functions are used.

Now do this:

1. In cell A11 type this: =Sum(A1:A10*B1:B10)
2. Now hit enter/return key
   
   This will give you an error as though we have used the range in this function, but it has not been used as an array function.

Let us repeat the above steps, but with a simple change:

1. In cell A11 type this, but do not hit enter): =Sum(A1:A10*B1:B10)
2. Now hold Ctrl+Shift keys and while holding them, hit enter/return key
   
   A function is used as an array function by inputting the function using Ctrl+Shift keys. These two keys will display curly brackets '{}' around the function, but these actually are not there in the cell. They just show up in the cell.

Now cell A11 will show the result of the function as 385. This function worked something like this - it multiplies each cell from range 1 and range 2, and then adds up the products to get the sum.

Next: Ways in which Array Functions can be used

Share on Facebook Share on Twitter Share on Google Plus

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.