**Understanding Arrays**

For those who do not have a background in programming or mathematics, the expression *Array* may not be familiar.

So what exactly is an *Array*?

For our purposes, an *Array* is simply a set of values which can be stored in a formula, a range of cells, or the computer’s memory.

The size of an *Array* can range from two to thousands of values.

**Using Arrays in Formulas**

There are several different types of *Arrays* used by Excel when working with formulas:

An *Array* stored in a Worksheet in a range of cells: For example, when the **SUM** function sums the values stored in range of cells, it is treating those values as an *Array*.

An *Array* stored in a formula:

Instead of entering cell addresses to enable a formula to operate on the values stored in those locations, you may enter an *Array* of values into the **SUM** function arguments: =SUM(1,2,3,4,5).

OR

Enter an *Array* enclosed in brackets into the formula argument. For example, use the **MATCH** function to return the position of the number 10 in an *Array* of values: =MATCH(10,{3,7,10,15,20}). The result = 3

Excel formulas create *Arrays* to store values:

Formulas such as **SUMPRODUCT** utilize computer memory to store values temporarily while calculating complicated math problems.

These values are stored in an *Array*.

Example:

To add the total sales amount of 3 items when the quantities sold are 10, 20, and 30 and the sale prices are $3, $4, and $5 respectively, the

**SUMPRODUCT** formula stores each multiplication product in an *Array* (*Array* size is 3) and then adds the three values from the *Array*.

The **SUMPRODUCT** formula: =SUMPRODUCT(A1:A3,B1:B3),

Result - total sales=$260.

Let Excel create an *Array* *fo**rmula*:

As explained in the previous section, many formulas create *Arrays *when they need to store values during calculations. However, an Excel user may create a formula that deliberately enforces the program to open an *Array*/*Arrays* to store values.

Example:

Use the **SUM** function to return total sales (see previous example).

The formula will now look like this: {=SUM(A1:A3*B1:B3)}, Result total sales=$260.

To apply an *Array* *formula*:

Enter the formula, select the cell, press <**F2**>, and then simultaneously press <**Ctrl+Shift+Enter**>.