Create an array formula

You can use an array formula to perform several calculations to generate a single result. This type of array formula can simplify a worksheet 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.

   

   Array formula that produces a single result

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

3. Press CTRL+SHIFT+ENTER.

Some worksheet 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 (column B) for a series of three months (column A), the TREND function determines the straight-line values for the sales figures. To display all of the results of the formula, it is entered into three cells in column C (C1:C3).

   

   Array formula that produces multiple results

   When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate results (22196, 17079, and 11962), based on the three sales figures and the three months.

3. Press CTRL+SHIFT+ENTER.