About array formulas and array constants

To calculate a single result This type of array formula can simplify a spreadsheet model by replacing several different formulas with a single array formula.

For example, the following array formula calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the individual values for each stock.

When you enter the formula =SUM(B2:C2*B3:C3) as an array formula, it multiples the Shares (in cells B2 and C2) and Price (in cells B3 and C3) for each stock, and then adds the results of those calculations together.

To calculate multiple results Some spreadsheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments.

For example, given a series of three sales figures (20234, 21003, and 10000 in column B) for a series of three months (1, 2, and 3 in column A), the TREND function determines the straight-line values for the sales figures. To display all of the results of this formula, you can enter the formula into three cells in column C (C1:C3).

When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate results (22196, 17079, and 11962, which would be shown in cells C1, C2, and C3), based on the three sales figures and the three months.

In an ordinary formula, you can enter a reference to a cell containing a value, or you can enter the value itself, which is also called a constant. Similarly, in an array formula, you can enter a reference to an array, or you can enter the array of values contained within the cells, which is also called an array constant. Array formulas accept constants in the same way that nonarray formulas do, but you must enter the array constants in a certain format.

Array constants can contain numbers, text, logical values such as TRUE or FALSE, or error values such as #N/A. Different types of values can be in the same array constant— for example, {1,3,4;TRUE,FALSE,TRUE}. Numbers in array constants can be in integer, decimal, or scientific format. Text must be enclosed in double quotation marks— for example, "Tuesday".

Array constants cannot contain cell references, columns or rows of unequal length, formulas, or the special characters $ (dollar sign), parentheses, or % (percent).

The format of array constants

Array constants are enclosed in braces ( { } ).

Separate values in different columns with commas (,). For example, to represent the values 10, 20, 30, and 40, enter {10,20,30,40}. This array constant is known as a 1-by-4 array and is equivalent to a 1-row-by-4-column reference.

Separate values in different rows with semicolons (;). For example, to represent the values 10, 20, 30, and 40 in one row and 50, 60, 70, and 80 in the row immediately below, you would enter a 2-by-4 array constant: {10,20,30,40;50,60,70,80}.