An array is range or group of related data values.
In a spreadsheet program such as Excel the values in an array are normally stored in adjacent cells.
An array formula is a formula that includes an array in its makeup.
Types of Array Formulas
There are two types of array formulas:
- multi cell array formula - the same formula is located in more than one cell in a worksheet
- single cell array formula one formula that carries out multiple calculations in a single worksheet cell
Multi Cell Array Formulas
The formula performs the same calculation in each cell it is located in, but each instance of the formula uses different data in its calculations and each instance produces different results.
An example of a multiple cell array formula would be:
{=A1:A2 * B1:B2}
If the above example is located in cells C1 and C2 in a worksheet then the following results would be:- the data in A1 is multiplied by the data in B1 and the results stored in cell C1
- the data in A2 is multiplied by the data in B2 and the results stored in cell C2
Single Cell Array Formulas
An example of a single cell array formula would be:
=SUM {A1:A2 * B1:B2}
This formula adds together the product of A1*B1 and A2*B2 and returns a single result in a single cell in the worksheet.
Another way of writing the above formula would be:
= (A1 * B1) + (A2 * B2)
CSE Formulas
Array formulas are surrounded by curly braces "{ } ". These are added to a formula by pressing the CTRL, SHIFT, and ENTER keys after typing the formula into a cell or cells.
For this reason an array formula is sometimes referred to as a CSE formula in Excel.
