**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 computers memory.

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

**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 a 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 formulas 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 formula:**

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.