Sharing Information: Tutorial : Array Formulas in Excel

Tutorial : Array Formulas in Excel

10:21 PM |

Diposkan oleh Fiola |

Arrays in Microsoft Excel are not a single function, but actually a variety of different formulas that you can use to simplify your work with arrays.

Arrays in Microsoft Excel are not a single function, but actually a variety of different formulas that you can use to simplify your work with arrays. There are two main things to remember when working with arrays in Excel:

Array formulas perform computations on a series of values.
When you are done entering an array formula, you have to press CTRL, SHIFT, and ENTER all at the same time instead of just ENTER.

The output of an array formula can be either a single value, or another array, depending on the formula that you use. When you enter an array formula by pressing CTRL, SHIFT, and ENTER, it will show up with braces { } around it.

Array Formulas that Return Arrays as their Output

Sometimes when you have a long series of values that you want to work with, it can be helpful to use an array formula to perform operations on every element of the array at once instead of filling in the formula in one cell and then having to fill in all of the other cells. In the example above, the array of interested is ARRAY 1 in cells A3:A7. If you’re interested in returning the row that corresponds to each element of this array, you can highlight cells C3:C7 and type =ROW(A3:A7), then press CNTRL, SHIFT, and ENTER together. The output is shown above as OUTPUT 1. This type of formula can be useful if you need to get a series of references based on where the components of the array are located.

Another way to use array formulas is to perform functions on multiple values at the same time. In the case of OUTPUT 2 above, cells D3:D7 were selected, =(A3:A7) was entered, followed by pressing CTRL, SHIFT, and ENTER. This returns all of the values from the original array. Similarly, you could instead enter =(A3:A7)*2 and press CTRL, SHIFT, and ENTER together. This would return the output shown as OUTPUT 3 above.

Array Formulas that Return a Single Value as Their Output

Array formulas can also be useful for performing operations on arrays where certain criteria are met. The example below shows how an equation entered in cell D6 returns the sum of all of the elements in Array 1 which are greater than 5. The function itself is =SUM(IF(A3:A11>5,A3:A11,FALSE)). This takes each element in the array in cells A3:A11 and creates a new array by checking to see if it is greater than 5. In cases where the value is greater than 5, it returns the value, otherwise it returns FALSE. So the intermediate step would show = SUM(FALSE, 16, FALSE, FALSE, 9, FALSE, 26, 6, 10). The SUM function ignores the FALSE values, so the result is 16+9+26+6+10 = 67.

You can also include multiple criteria if you want to. The next example looks at a list of transactions that shows the region and product as well as the number of units sold. If you wanted to know how many total oranges were sold in the North region, you could use the array formula =SUM((A2:A10=”North”)*(B2:B10=”Oranges”)*C2:C10) followed by pressing CTRL, SHIFT, and ENTER together. This formula looks at 3 arrays separately, evaluating the first 2 arrays based on the criteria entered, and the third array just for its values.

The criteria are effectively evaluated as IF statements, returning true where the criteria is met and FALSE where it is not. So, the equation would evaluate as =SUM({TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}*{TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, TRUE, FALSE}*{4, 6, 8, 9, 3, 14, 11, 8, 16}). TRUE values evaluate as 1’s, while FALSE values evaluate as 0’s, so the next step would look like =SUM(4,0,0,0,0,0,0,8,0). This produces the result of 12, which is the total number of oranges sold in the North region. The array formula allows you take several steps of computation and combine them into a single formula.

In addition to the SUM function, you can also use a variety of other equations like MAX, MIN, and AVERAGE in array functions depending on your needs. If you understand what result you’re looking for and can set up the array function correctly, you can save yourself a lot of work by letting Excel handle the complexity of the calculations.