1. Computing

Array Formula

By

Excel Array Formula Examples

Excel Array Formula Examples

© Ted French
Definition:

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 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.

©2014 About.com. All rights reserved.