Excel > Excel Stuff > Excel Tips And Tricks > Array Formula Examples

This workbook demonstrates numerous examples of one of the most powerful, but underused, features of Excel: array formulas. An array formula works on whole ranges of the worksheet at once. Suppose that we have two ranges of numbers: 1 2 3 4

5 6 7 8

Now, suppose that as an input to a formula, we need to know the product of the corresponding values in each array (e.g., 1*5, 2*6, etc). We can create an array of these products with A1:A4*B1:B4 (assuming the original values are in those ranges). The resulting array would be {5,12,21,32}. The example to the right shows how this works. Suppose that we need to know the results of dividing each number in K4:K13 by L4:L13. We could create in M4 the formula: =K4/L4 and then copy this down. That would give us the result that we need. Alternatively, we could create an array. First select M4:M13. Now, type in the formula: =K4:K13/L4:L13 and then hold down Ctrl and Shift while pressing the Enter key. If you look at one of the cells in the array, you will see that the formula is: {=K4:K13/L4:L13}. Notice that Excel has added the curly brackets indicating that this is an array formula. If you were to just press the Enter key (neglecting to hold down Ctrl and Shift) you would get only the value in M4. So, what's the advantage? Well, in this case nothing really. However, as we'll see, array formulas are much more efficient than regular formulas in two ways: 1) They save space in the worksheet. Using array formulas we can eliminate columns showing intermediate results. This also makes the worksheet easier to read because it isn't cluttered up by unnecessary information. 2) They calculate more quickly than the equivalent series of individual formulas. Follow along with the examples, and your spreadsheet skills will improve markedly.

Number Divisor 10 10 20 10 30 10 40 10 50 10 60 10 70 10 80 10 90 10 100 10

Resulting Array 1 2 3 4 5 6 7 8 9 10

Weight 0.1 0.2 0.4 0.2 0.1 Weighted Average Variance Standard Deviation

Score 95 85 75 65 55 75 120 10.95

What this spreadsheet does:

This sheet demonstrates how to calculate a weighted average, variance, and standard d array formulas. Note that I could have used the SumProduct function to calculate the average, but Excel has no function to calculate standard deviation when the weights ar

Had I used normal formulas, I would have had to add a column or two to do intermedi The array functions are much more efficient in terms of speed of calculation and in the spreadsheet space they consume.

How the array functions work:

Weighted Average: Array functions work on whole ranges at a time, rather than individual cells. The weig the most simple. The formula is =SUM(A2:A6*B2:B6). First, the formula says "take A2:A6 and multiply it by the corresponding value in B2:B6. This creates an array (or Sum function simply calculates the total of all of the elements of the array.

Variance: This is a bit more complicated, but the logic is similar. The formula is =SUM((B2:B6 AVERAGE(B2:B6))^2*A2:A6). To calculate the variance, we subtract the average of outcomes from each possible outcome, square the difference, multiply by the probabli and finally sum the results. Let's break this formula down to its parts in order of calcu

The first part takes each value in B2:B6 and subtracts the average of these values. The of deviations from the mean. Next, each value in this array is squared resulting in an a deviations from the mean. Now, we calculate a weighted average of these squared dev multiplying each squared deviation by its probability of occurance and summing the re

Standard Deviation: The standard deviation is calculated by finding the square root of the variance. The ea this would be to use =sqrt(B8), but we don't have to use a cell for the variance calculat we only care about the standard deviation, so we would use the formula: =SQRT(SUM AVERAGE(B2:B6))^2*A2:A6)). This is the same formula as before, but we have add function around the whole thing.

ge, variance, and standard deviation using ct function to calculate the weighted viation when the weights are unequal.

umn or two to do intermediate calculations. eed of calculation and in the amount of

n individual cells. The weighted average is First, the formula says "take each value in 6. This creates an array (or vector), and the nts of the array.

e formula is =SUM((B2:B6, we subtract the average of all possible ce, multiply by the probablility of occurance, to its parts in order of calculation:

verage of these values. The result is an array y is squared resulting in an array of squared verage of these squared deviations by curance and summing the results.

root of the variance. The easiest way to do cell for the variance calculation. Normally, e the formula: =SQRT(SUM((B2:B6a as before, but we have added the SQRT()

Product A B C A B C A B C A B C A B C A B C

Month January January January February February February March March March April April April May May May June June June

Unit Sales 25 50 17 20 42 26 19 48 21 16 35 19 24 52 23 21 47 22

Product A B C

Total Units 125 274 128

Month January February March April May June

Total Units 92 88 88 70 99 90

What this spreadsheet does: We have a list of products and their unit sales by month. We wish to determine h of each product were sold over the entire six-month period. Additionally, we wis know how many units were sold during each of the six months.

How the array functions work: To calculate the total sales of Product A, the formula is: =SUM(($A$2:$A$19=A22)*($C$2:$C$19)). This is less complicated if we break into parts:

The first part, ($A$2:$A$19=A22), creates an array of True/False (boolean) value in mind that True = 1 and False = 0, so we have an array of 0's and 1's. Excel loo each cell in A2:A19 and determines if the value is the same as that in A22, in this "A". If it is, it puts a 1 in the array, if not it puts in a 0. So, we get an array that lo this: {1,0,0,1,0,0,1,0,0,1,0,0,1,0,0,1,0,0}.

Next, we multiply this array by the array of unit sales in C2:C19. Obviously, mul anything by 0 returns a 0, so the new array will be populated by sales for January will look like this: {25,0,0,20,0,0,19,0,0,16,0,0,24,0,0,21,0,0}.

Finally, the Sum function simply calculates the total of each of the values in this a For Product A it is 125 units. Each of the other two array formulas work in exacly same way, except that the product name is different. Calculating monthly sales works in exactly the same way, except that the criteria name of the month rather than the name of the product.

Note that we could sort the list in any way (by product, by month, or by units) and formulas will still work perfectly.

by month. We wish to determine how many month period. Additionally, we wish to of the six months.

ormula is: This is less complicated if we break it down

array of True/False (boolean) values. Keep e an array of 0's and 1's. Excel looks at e is the same as that in A22, in this case ts in a 0. So, we get an array that looks like

it sales in C2:C19. Obviously, multiplying l be populated by sales for January only. It 0,24,0,0,21,0,0}.

e total of each of the values in this array. r two array formulas work in exacly the erent.

e same way, except that the criteria is the product. product, by month, or by units) and our

Year 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001

Sales 1205 1275 1346 1395 1500 1452 1520 1592 1675 1734 1793 1846

What this spreadsheet does: We have a listing of sales year by year over the past 12 years and wish to k compound average annual rate of sales growth has been over this period. calculate the standard deviation of sales growth.

Note that we need to deal with growth rates, not dollar growth of sales. Fo forecasting purposes, the growth rates are much closer to the ideals of stat and variance than are the dollar amounts. Also, we really should use the g rate of growth, not the arithmetic (typical) rate of growth.

How the array functions work: There's really nothing too complicated (or new) in these functions. In the we use a built-in function on the array of growth rates. To calculate the ar use the formula: B3:B13/B2:B12-1. This takes each cell in B3:B13 and d corresponding value in B2:B12. We then subtract 1 to get the percentage c

Using Built-in Functions and Arrays Arithmetic Average Growth Rate 3.99% Compound Average Growth Rate 3.95% The alternative array formulas are a little more complicated, but the idea i advantage to using them, I'm merely showing how it could be done. At th Standard Deviation of Growth Rate 2.73% able to figure them out. Alternative Array Formulas Arithmetic Average Growth Rate Compound Average Growth Rate Standard Deviation of Growth Rate

3.99% 3.95% 2.73%

er the past 12 years and wish to know what the owth has been over this period. It also shows how to rowth.

es, not dollar growth of sales. For analysis and much closer to the ideals of stationarity in the mean Also, we really should use the geometric (compound) ) rate of growth.

r new) in these functions. In the first set of formulas, growth rates. To calculate the array of growth rates, we takes each cell in B3:B13 and divides it by the subtract 1 to get the percentage change.

more complicated, but the idea is the same. There's no wing how it could be done. At this point, you should be

DATE S&P 500 Dec-96 740.74 Jan-97 786.16 Feb-97 790.82 Mar-97 757.12 Apr-97 801.34 May-97 848.28 Jun-97 885.14 Jul-97 954.31 Aug-97 899.47 Sep-97 947.28 Oct-97 914.62 Nov-97 955.40 Dec-97 970.43 Jan-98 980.28 Feb-98 1049.34 Mar-98 1101.75 Apr-98 1111.75 May-98 1090.82 Jun-98 1133.84 Jul-98 1120.67 Aug-98 957.28 Sep-98 1017.01 Oct-98 1098.67 Nov-98 1163.63 Dec-98 1229.23 Jan-99 1279.64 Feb-99 1238.33 Mar-99 1286.37 Apr-99 1335.18 May-99 1301.84 Jun-99 1372.71 Jul-99 1328.72 Aug-99 1320.41 Sep-99 1282.71 Oct-99 1362.93 Nov-99 1388.91 Dec-99 1469.25 Jan-00 1394.46 Feb-00 1366.42 Mar-00 1498.58 Apr-00 1452.43 May-00 1420.60 Jun-00 1454.60 Jul-00 1430.83 Aug-00 1517.68 Sep-00 1436.51

IBM 37.88 39.22 35.94 34.31 40.13 43.25 45.13 52.88 50.69 53.00 49.25 54.75 52.31 49.38 52.22 51.94 57.94 58.75 57.41 66.25 56.31 64.25 74.25 82.56 92.19 91.63 84.88 88.63 104.59 116.00 129.25 125.69 124.56 121.00 98.25 103.06 107.88 112.25 102.75 118.38 111.50 107.31 109.56 112.25 132.02 112.63

GM 55.75 59.00 57.88 55.38 57.88 57.38 55.75 61.88 62.75 66.94 64.19 60.94 60.75 57.94 68.94 67.75 67.38 71.88 66.81 72.31 58.13 54.88 63.19 69.88 71.56 89.75 82.56 87.00 89.06 69.00 66.00 61.13 66.25 62.94 70.44 73.80 72.69 80.56 76.06 82.81 93.63 70.63 58.06 56.94 70.00 65.00

Total Return Monthly Return Standard Deviation Coef. of Variation Beta

S&P 500 IBM 55.19% 227.10% 0.74% 1.99% 5.16% 10.36% 7.02 5.19 1.00 1.24

GM -13.72% -0.25% 10.80% (43.96) 1.14

What this spreadsheet does: We wish to compare two stocks (IBM and GM) to each other and a br The comparison consists of total return over the five-year period, com return, standard deviation of monthly returns, coefficient of variation

This worksheet demonstrates the efficiency of array functions. If we would have to add three columns to calculate the monthly returns. Th space in the worksheet, but it would require an additional 180 calcula clutter the worksheet.

There are no new techniques here, the calculations all use the same te "Sales Growth" worksheet.

Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01

1429.40 1314.95 1320.28 1366.01 1239.94 1160.33 1249.46 1255.82 1224.38 1211.23 1133.58 1040.94 1059.78 1139.45 1149.56

98.50 93.50 85.00 112.00 99.90 96.18 115.14 111.80 113.50 105.21 99.95 91.72 108.07 115.59 123.89

62.13 49.50 50.94 53.70 53.32 51.85 54.81 56.90 64.35 63.60 54.75 42.90 41.32 49.70 48.10

Historical stock prices provided by CSI, Inc. Historical mutual fund and industry prices provided by Media General Financial

GM) to each other and a broad market index (S&P 500). r the five-year period, compound average monthly s, coefficient of variation of monthly returns, and betas.

of array functions. If we used non-array functions, we te the monthly returns. This would not only take more an additional 180 calculations and would needlessly

lations all use the same technique as shown in the

Media General Financial Services.