Create an array formula

You can use an array formula to perform several calculations to generate a single result. This type of formula can simplify a spreadsheet model by replacing several different formulas with a single array formula.

1. Click the cell in which you want to enter the array formula.
2. Type the array formula.

   For example, the following 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.

3. Press CTRL+SHIFT+ENTER.

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

1. Select the range of cells in which you want to enter the array formula.
2. Type the array formula.

   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, enter it 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.

3. Press CTRL+SHIFT+ENTER.