Array (Excel Function - Conceptual)

Objective

Array is a very powerful tool that can enhance power of normal excel functions manifold. Arrays are also used in VBA to store and retrieve data.

Most of the times, my audience is non-IT people, who are not familiar with Array concept. So here it is in layman language.

Prerequisites

The reader/user should have basic idea of Excel Functions.

Solution Concept

This post/learning focuses on Function side of arrays. VBA will be covered separately.

Array (definition): An array is collection of data/values. For example “a b c d e” is an array of alphabets.

Following picture shows an array of antennas:

Let’s do it

1.1.      Array in Excel Functions

1.1.1. A normal Excel equation generates ONE result. Whereas an array equation generates more than one result. For example =SUM(A25:A35) will give you one number – it’s a standard Excel Function.

1.1.2. Whereas, an array equation is capable of generating more than one results. To see these results you have TWO choices; 1) you can display results on multiple cells 2) convert the results into one number by using SUM, AVERAGE, COUNT, etc.

1.1.3.  All Array equations are completed in by pressing three keys simultaneously i.e. {Shift}, {Ctrl} and {Enter}. A normal excel equation only requires {Enter} key to be pressed.

Once Entered, you will notice that in formula bar “{” and “}” are added to the equation.

These braces/brackets represent array equation.

If you edit array equation, you have to press three keys again after the editing.

1.1.4. Multiple Cell Array: As array generates number of results, one way is to spread the array over number of cells equal to expected results.

In this method, an array equation is created in one cell then it is copied to other cells (can also be done through mouse drag). Limitation: Full column reference like “A:A” cannot be used with drag/copy paste, you need to have specific reference like A2.

Video

Another way is to select target cells (starting from Row 1), then enter equation in Row 1, it will be copied to all cells automatically.

Once array formula is copied on multiple cells, All such cells become part of one group, this means you cannot amend/delete one cell you have to apply any change to whole group.

Now consider this equation: =IF(B:B=”Tokyo”,C:C,0)

It means if word “Tokyo” is found in any row of column B then pick corresponding value from Column C otherwise display Zero. This equation is not Cell Specific but Column Specific.

Video

1.1.5. Single Cell Array:

The above equation generates multiple results, if we use SUM with this equation all results will be added up and we will be able to display the result in single cell =SUM(IF(B:B=”Tokyo”,C:C,0)).

Above equation will act as SUMIF function. Do remember to enter Array function by pressing three keys.

1.1.6. In attached Sample Workbook the ‘Practical’ Sheet will give you an insight on how powerful is array function.

Sample Workbook          

Prerequisites/See also (Links not activated yet)