Chapter 74: Array Formulas and Functions

Overview

• What is an array formula?

• How do we interpret formulas such as (D2:D7)*(E2:E7) and SUM(D2:D7*E2:E7)?

• I have a list of names in one column. These names change often. Is there any easy way to transpose the listed names to one row so that changes in the original column of names are reflected in the new row?

• I have a list of monthly stock returns. Is there a way to determine the number of returns from –30 percent through –20 percent, –10 percent through 0 percent, and so on that will automatically update if I change the original data?

• Can I write one formula that will sum up the second digit of a list of integers?

• Is there a way to look at two lists of names and determine which names occur on both lists?

• Can I write a formula that averages all numbers in a list that exceed the list’s median value?

• I have a sales database for a small makeup company that lists the salesperson, product, units sold, and dollar amount for every transaction. I know I can use database statistical functions to summarize this data, but can I also use array functions to summarize the data and answer questions such as how many units of makeup a salesperson sold, how many units of lipstick were sold, and how many units were sold by a specific salesperson or were lipstick?

• What are array constants and how can I use them?

• How do I edit array formulas?

• Given quarterly revenues for a toy store, can I estimate the trend and seasonality of the store’s revenues?

• What is an array formula?

• Array formulas often provide a shortcut or more efficient approach to performing complex calculations with Microsoft Office Excel. An array formula can return a result in either one cell or in a range of cells. Array formulas perform operations on two or more sets of values, called array arguments. Each array argument used in an array formula must contain exactly the same number of rows and columns.

• When entering an array formula, you must first select the range in which you want Excel to place the array formula’s results. Then, after entering the formula in the first cell of the selected range, you must press Ctrl+Shift+Enter. If you fail to press Ctrl+Shift+Enter, you’ll obtain incorrect or nonsensical results. I’ll refer to the process of entering an array formula and then pressing Ctrl+Shift+Enter as array-entering a formula.

• Excel also contains a variety of array functions. You met two array functions (LINEST and TREND) in Chapter 47, “Introduction to Multiple Regression” and Chapter 48, “Incorporating Qualitative Factors into Multiple Regression.” As with an array formula, to use an array function you must first select the range in which you want the function’s results placed. Then, after entering the function in the first cell of the selected range, you must press Ctrl+Shift+Enter. In this chapter, I’ll introduce you to three other useful array functions: TRANSPOSE, FREQUENCY, and LOGEST.

• As you’ll see, you cannot delete any part of a cell range that contains results computed with an array formula. Also, you cannot paste an array formula into a range that contains both blank cells and array formulas. For example, if you have an array formula in cell C10 and you want to copy it to the cell range C10:J15, you cannot simply copy the formula to this range because the range contains both blank cells and the array formula in cell C10. To work around this difficulty, copy the formula from C10 to D10:J10 and then copy the contents of C10:J10 to C11:J15.

• The best way to learn how array formulas and functions work is by looking at some examples, so let’s get started.

• How do we interpret formulas such as (D2:D7)*(E2:E7) and SUM(D2:D7*E2:E7)?

• In the Total Wages worksheet in the Arrays.xlsx file, I’ve listed the number of hours worked and the hourly wage rates for six employees, as you can see in Figure 74-1.

  
  Figure 74-1: Using array formulas to compute hourly wages

  If we wanted to compute each person’s total wages, we could simply copy from F2 to F3:F7 the formula D3*E3. There is certainly nothing wrong with that approach, but using an array formula provides a more elegant solution. Begin by selecting the range F2:F7, where you want each person’s total earnings to be computed. Then enter the formula =(D2:D7*E2:E7) and press Ctrl+Shift+Enter. You will see that each person’s total wages has been correctly computed. Also, if you look at the formula bar, you’ll see that the formula appears as {=(D2:D7*E2:E7)}. The curly brackets are the way Excel tells us that we’ve created an array formula. (You don’t enter the curly brackets that show up at the beginning and end of an array formula, but to indicate that a formula is an array formula in this chapter, we’ll show the curly brackets.)

• To see how this formula works, click in the formula bar, highlight D2:D7 in the formula, and then press F9. You will see {3;4;5;8;6;7}, which is the way Excel creates the cell range D2:D7 as an array. Now select E2:E7 in the formula bar, and then press F9 again. You will see {6;7;8;9;10;11}, which is the way Excel creates an array corresponding to the range E2:E7. The inclusion of the asterisk (*) tells Excel to multiply the corresponding elements in each array. Because the cells’ ranges we are multiplying include six cells each, Excel creates arrays with six items, and because we selected a range of six cells, each person’s total wage is displayed in its own cell. Had we selected a range of only five cells, the sixth item in the array would not be displayed.

• Suppose we want to compute the total wages earned by all employees. One approach would be to use the formula =SUMPRODUCT(D2:D7,E2:E7). Again, however, let’s try to create an array formula to compute total wages. We begin by selecting one cell (I chose cell G2) in which to place our result. Then we enter in cell G2 the formula =SUM(D2:D7* E2:E7). After pressing Ctrl+Shift+Enter, we obtain (3)(6)+(4)(7)+(5)(8)+(8)(9)+(6)(10)+ (7)(11)=295. To see how this formula works, select the D2:D7*E2:E7 portion in the formula bar and then press F9. You will see SUM({18;28;40;74;60;77}), which shows that Excel created a six-element array whose first element is 3*6(18), whose second element is 4*7(28), and so on, until the last element, which is 7*11 (77). Excel then adds up the values in the array to obtain the total of $295.

• I have a list of names in one column. These names change often. Is there any easy way to transpose the listed names to one row so that changes in the original column of names are reflected in the new row?

• In the Transpose worksheet in the Arrays.xlsx file, shown in Figure 74-2 on the next page, I’ve listed a set of names in cells A4:A8. We want to list these names in one row (the cell range C3:G3). If we knew that the original list of names would never change, we could accomplish this goal by copying the cell range and then using the Transpose option in the Paste Special dialog box. (See Chapter 13, “The Paste Special Command,” for details.) Unfortunately, if the names in column A change, the names in the row 3 would not reflect those changes if we use Paste Special, Transpose. What we need in this situation is the TRANSPOSE function of Excel.

  
  Figure 74-2: Using the TRANSPOSE function

• The TRANSPOSE function is an array function that changes rows of a selected range into columns and vice versa. To begin using TRANSPOSE, we select the range C3:G3, where we want our transposed list of names to be placed. Then, in cell C3, we array-enter the formula =TRANSPOSE(A4:A8). The list of names is now displayed in one row. More importantly, if we change any of the names in A4:A8, the corresponding name will change in the transposed range.

• I have a list of monthly stock returns. Is there a way to determine the number of returns from –30 percent through –20 percent, –10 percent through 0 percent, and so on that will automatically update if I change the original data?

• This problem is a job for the FREQUENCY array function. The FREQUENCY function counts how many values in an array (called the data array) occur within given value ranges (specified by a bin array). The syntax of the FREQUENCY function is FREQUENCY(data array,bin array).

• To illustrate the use of the FREQUENCY function, look at the Frequency worksheet in the Arrays.xlsx file, shown in Figure 74-3. I’ve listed monthly stock returns for a fictitious stock in the cell range A4:A77.

  
  Figure 74-3: Using the FREQUENCY function

• We found in cells A1 and A2 (using the MIN and MAX functions) that all returns are from –44 percent through 53 percent. Based on this information, I set up our bin value boundaries in cells C7:C17, starting at –0.4 and ending at 0.6. Now I select the range D7:D18, where I want the results of the FREQUENCY function to be placed. In this range, cell D7 will count the number of data points less than or equal to –0.4, D8 will count the number of data points greater than –0.4 and less than or equal to –0.3, and so on. Cell D17 will count all data points greater than 0.5 and less than or equal to 0.6, and cell D18 will count all the data points that are greater than 0.6.

• I enter the formula =FREQUENCY(A4:A77,C7:C17), and then press Ctrl+Shift+Enter. This formula tells Excel to count the number of data points from A4:A77 (the data array) that lie in each of the bin ranges defined in C7:C17. We find, for example, that one return is greater than –0.4 and less than or equal to –0.3. Thirteen returns are greater than 0.1 and less than or equal to 0.2. If you change any of the data points in the data array, the results generated by the FREQUENCY function in cells D7:D17 will reflect the changes in your data.

• Can I write one formula that will sum up the second digit of a list of integers?

• In the cell range A4:A10 in the Sum Up 2nd Digit worksheet in the Arrays.xlsx file, I’ve listed seven integers. (See Figure 74-4.) We would like to write one formula that will sum up the second digit of each number. Of course, we could obtain this sum by copying from B4 to B5:B10 the formula VALUE(MID(A4,2,1)). This formula returns (as a numerical value) the second character in cell A4. Then we could add up the range B4:B10 and obtain the total of 27.

  
  Figure 74-4: Summing second digits in a set of integers

• An array function makes this process much easier. Simply select cell C7 and array-enter the formula =SUM(VALUE(MID(A4:A10,2,1)). Your array formula will return the correct answer, 27.

• To see what this formula does, highlight MID(A4:A10,2,1) in the formula bar and then press F9. You will see {"4";"5";"6";"6";"0";"3";"3"}. This string of values shows that Excel has created an array consisting of the second digit (viewed as text) in the cell range A4:A10. The VALUE portion of the formula changes these text strings into numerical values, which are added up by the SUM portion of the formula.

• Notice that in cell A11, I entered a number with one digit. Because this number has no second digit, the MID portion of our formula returns #VALUE. How can we modify our array formula to account for the possible inclusion of one-digit integers? Simply array-enter in cell E8 the formula {SUM(IF(LEN(A4:A11)>=2,VALUE(MID(A4:A11,2,1)),0))}. This formula replaces any one-digit integer with a 0, so we still obtain the correct sum.

• Is there a way to look at two lists of names and determine which names occur on both lists?

• In the Matching Names worksheet in the Arrays.xlsx file, I’ve included two lists of names (in columns D and E), as you can see in Figure 74-5. We want to determine which names on List 1 also appear on List 2. To accomplish this, we select the range C5:C28 and array-enter in cell C5 the formula {MATCH(D5:D28,E5:E28,0)}. This formula loops through the cells C5:C28. In cell C5, the formula verifies whether the name in D5 has a match in column E. If a match exists, the formula returns the position of the first match in E5:E28. If no match exists, the formula returns #NA (for not available). Similarly in C6, the formula verifies whether the second name on List 1 has a match. We see, for example, that Artest does not appear on the second list but Harrington does (first matched in the second cell in the range E5:E28).

  
  Figure 74-5: Finding duplicates in two lists

• To enter Yes for each name in List 1 with a match in List 2, and No for each List 1 name without a match, select the cell range B5:B28 and array-enter the formula {IF(ISERROR(C5:C28),"No","Yes")} in cell B5. This formula displays No for each cell in C5:C28 containing the #NA message and Yes for all cells returning a numerical value. Note that =ISERROR(x) yields True if the formula x evaluates to an error and yields False otherwise.

• Can I write a formula that averages all numbers in a list that are greater than or equal to the list’s median value?

• In the Average Those > Median worksheet in the Arrays.xlsx file, shown in Figure 74-6, the range D5:D785 (named Prices) contains a list of prices. We’d like to average all prices that are at least as large as the median price. In cell F2, I’ve computed the median with the formula Median(prices). In cell F3, I compute the average of numbers greater than or equal to the median by entering the formula =SUMIF(prices,">="&F2,prices)/COUNT-IF(prices,">="&F2). This formula adds up all prices that are at least as large as the median value (243) and then divides by the number of prices that are at least as large as the median. We find that the average of all prices at least as large as the median price is $324.30.

  
  Figure 74-6: Averaging prices at least as large as median price

• An easier approach is to select cell F6 and array-enter the formula =AVERAGE(IF (prices>=MEDIAN(prices),prices,""))}. This formula creates an array that contains the row’s price if the row’s price is greater than or equal to the median price or a space otherwise. Averaging this array gives us the results we want.

• I have a sales database for a small makeup company that lists the salesperson, product, units sold, and dollar amount for every transaction. I know I can use database statistical functions to summarize this data, but can I also use array functions to summarize the data and answer questions such as how many units of makeup a salesperson sold, how many units of lipstick were sold, and how many units were sold by a specific salesperson or were lipstick?

• The Makeuparray.xlsx file contains a list of 1900 sales transactions made by a makeup company. For each transaction, the transaction number, salesperson, transaction date, product sold, units sold, and dollar volume are listed. You can see some of the data in Figure 74-7.

  
  Figure 74-7: Makeup database

• This data can easily be summarized by using database statistical functions, as I described in Chapter 39, “Summarizing Data with Database Statistical Functions,” or by using COUNTIFS and SUMIFS functions (see Chapter 18, “The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions,” and Chapter 19, “The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions”). As you’ll see in this section, array functions provide an easy, powerful alternative to database statistical functions.

How many units of makeup did Jen sell?   We can easily answer this question by using the SUMIF function. In this worksheet, I named the cell range J5:J1904 Name and the cell range M5:M1904 Units. I entered in cell E7 the formula SUMIF(Name,"Jen",Units) to sum up all the units sold by Jen. We found that Jen sold 9537 units. We can also answer this question by array-entering in cell E6 the formula =SUM(IF(J5:J1904="Jen",M5:M1904,0))}. This formula creates an array that contains the units sold for a transaction made by Jen and a 0 for all other transactions. Therefore, summing this array also yields the number of units sold by Jen, 9,537, as you can see in Figure 74-8.

Figure 74-8: Summarizing data with array formulas

How many units of lipstick did Jen sell?   This question requires a criterion that uses two columns (Name and Product). We could answer the question by using the database statistical function formula =DSUM(J4:N1904,4,E9:F10), which is entered in cell F7. This formula shows that Jen sold 1299 units of lipstick. We can also obtain this answer by using the array formula entered in cell F6, =SUM((J5:J1904="jen")*(L5:L1904="lipstick")*M5:M1904)}.

To understand this formula, you need to know a bit about Boolean arrays. The portion of this formula that reads (J5:J1904="jen") creates a Boolean array. For each entry in J5:J1904 that equals Jen, the array includes the value True, and for each entry in J5:J1904 that does not equal Jen, the array contains False. Similarly, the (L5:L1904="lipstick") portion of our formula creates a Boolean array with a True corresponding to each cell in the range that contains the word lipstick and a False corresponding to each cell in the range that does not. When Boolean arrays are multiplied, another array is created using the following rules:

• True*True=1

• True*False=0

• False*True=0

• False*False=0

In short, multiplying Boolean arrays mimics the AND operator. Multiplying the product of our Boolean arrays by the values in the range M5:M1904 creates a new array. In any row in which Jen sold lipstick, this array contains the units sold. In all other rows, this array contains a 0. Summing this array yields Jen’s total lipstick sales (1299).

How many units were sold by Jen or were lipstick?   In cell G7, I used the database statistical function =DSUM(J4:N1904,4,E12:F14) to find that all units that were sold by Jen or that were lipstick total 17,061. In cell G6, I computed the number of units that were sold by Jen or that were lipstick by array-entering the formula {SUM(IF((J5:J1904="jen")+(L5:L1904= "lipstick"),1,0)*M5:M1904)}.

Again, the portion of our formula that reads (J5:J1904="jen")+(L5:L1904="lipstick") creates two Boolean arrays. The first array contains True if and only if Jen (the formula is not case sensitive) is the salesperson. The second array contains True if and only if the product sold is lipstick. When Boolean arrays are added, the following rules are used:

• False+True=1

• True+True=1

• True+False=1

• False+False=0

In short, adding Boolean arrays mimics the OR operator. Therefore, our formula will create an array in which each row where Jen is the salesperson or lipstick is the product sold has the number of units sold multiplied by 1. In any other row, the number of units sold is multiplied by 0. We obtain the same result as the database statistical formula (17,061).

• Can I summarize the number of units of each product sold by each salesperson?

  Array formulas make answering a question such as this a snap. We begin by listing each sales-person’s name in the cell range A17:A25 and each product name in the cell range B16:F16. Now we array-enter in cell B17 the formula {SUM(($J$5:$J$1904=$A17)*($L$5:$L$1904=B$16)* $M$5:$M$1904)}.

• This formula counts only units of eye liner sold by Ashley (1920 units). By copying this formula to C17:F17, I compute the units of each product sold by Ashley. Next, I copy the formulas in C17:F17 to C18:C25 and compute the number of units of each product sold by each salesperson. Notice that I add a dollar sign to A in the reference to cell A17 so that I always pull the person’s name, and I add a dollar sign to the 16 in the reference to cell B16 so that I always pull the product.

  Note 

  The astute reader might ask why I simply didn’t select the formula in B17 and try to copy it in one step to fill in the table. Remember that you cannot paste an array formula into a range that contains both blank cells and array formulas, which is why I first copied the formula in B17 to C17:F17 and then dragged it down to complete the table.

• What are array constants and how can I use them?

• You can create your own arrays and use them in array formulas. Simply enclose the array values in curly brackets, { }. You need to enclose text in double quotation marks (" ") as well. You can also include the logical values True and False as entries in the array. Formulas or symbols such as dollar signs or commas are not allowed in array constants.

• As an example of how an array constant might be used, look at the Creating Powers worksheet in the Arrays.xlsx file, shown in Figure 74-9.

  
  Figure 74-9: Creating second and fourth powers of sales

• In this worksheet, we’re given sales during six months, and we want to create for each month the second, third, and fourth power of sales. Simply select the range D4:F9, which is where we want the resulting computation to be placed. Array-enter in cell D4 the formula =C4:C9^{2,3,4}. In the cell range D4:D9, this formula loops through and squares each number in C4:C9. In the cell range E4:E9, the formula loops through and cubes each number in C4:C9. Finally, in the cell range F4:F9, the formula loops through and raises each number in C4:C9 to the fourth power. The array constant {2,3,4} is required to let us loop through different power values.

• How do I edit array formulas?

• Suppose we have an array formula that creates results in multiple cells and we want to edit, move, or delete the results. You cannot edit a single element of the array. To edit an array formula, however, you can begin by selecting all cells in the array range. Then pick one cell in the array. By selecting F2 to edit a cell in the array, you can make changes in that cell. After making the changes, select Ctrl+Shift+Enter to enter your changes. Now the entire selected array will reflect your changes.

• Given quarterly revenues for a toy store, can I estimate the trend and seasonality of the store’s revenues?

• The Toysrustrend.xlsx file, shown in Figure 74-10 on the next page, contains quarterly revenues (in millions of dollars) for a toy store during the years 1997–2002. We would like to estimate the quarterly trend in revenues as well as the seasonality associated with each quarter (first quarter equals January–March; second quarter equals April–June; third Quarter equals July–September; fourth Quarter equals October–December). A trend of, for example, 1 percent per quarter means that sales are increasing at 1 percent per quarter. A seasonal index for the first quarter of, for example, 0.80 means that sales during Quarter 1 are approximately 80 percent of an average quarter.

  
  Figure 74-10: Toy revenue trend and seasonality estimation

• The trick to solving this problem is to use the LOGEST function of Excel. Suppose that we are trying to predict a variable y from independent variables x1, x2,…, xn, and we believe that for some values of a, b1, b2,…, bn, the relationship between y and x1, x2,…, xn is given by y=a(b1)^x1(b2)^x2(bn)^xn. (I’ll call this Equation 1.)

• The LOGEST function is used to determine values of a, b1, b2,…, bn that best fit this equation to the observed data. To use the LOGEST function to estimate trend and seasonality, note the following:

  • y equals quarterly revenues.

  • x1 equals the quarter number (listed in chronological order, the current quarter is Quarter 1, the next quarter is Quarter 2, and so on).

  • x2 equals 1 if the quarter is the first quarter of the year, and 0 otherwise.

  • x3 equals 1 if the quarter is the second quarter of the year, and 0 otherwise.

  • x4 equals 1 if the quarter is the third quarter of the year, and 0 otherwise.

• We need to choose one quarter to leave out of the model, and I’ve arbitrarily chosen the fourth quarter. This approach is similar to the one we used with dummy variables in Chapter 48. The model we choose to estimate is then y=a(b1)^x1(b2)^x2(b3)^x3(b4)^x4. When the LOGEST function determines values of a, b1, b2, b3, and b4 that best fit the data, the values are interpreted as follows:

  • a is a constant used to scale the forecasts.

  • b1 is a constant that represents the average per quarter percentage increase in toy store sales.

  • b2 is a constant that measures the ratio of first quarter sales to the omitted quarter’s (fourth quarter) sales.

  • b3 is a constant that measures the ratio of second-quarter sales to the omitted quarter’s sales.

  • b4 is a constant that measures the ratio of third-quarter sales to the omitted quarter’s sales.

  To begin, I created the dummy variables for Quarters 1–3 in the cell range G6:I27 by copying from G6 to G6:I27 the formula IF($D6=G$4,1,0). Remember that a fourth quarter is known to Excel because all three dummy variables equal 0 during the fourth quarter, which is why we can leave out the dummy variable for this quarter.

  We now select the cell range K6:O6, where we want LOGEST to place the estimated coefficients. The constant a will be placed in the right-most cell, followed by the coefficients corresponding to the ordering of the independent variables. Thus, the trend coefficient will be next to the constant, then the Quarter 1 coefficient, and so on.

  The syntax we will use for the LOGEST function is LOGEST(y range,x range,TRUE, TRUE). After array-entering in cell K6 the formula =LOGEST(E6:E27,F6:I27,TRUE, TRUE)}, we obtain the coefficient estimates shown in Figure 74-11. Our equation to predict quarterly revenues (in millions) is as follows:

   4219.57*(1.0086)quarter number*(.435)Q1dummy*(.426)Q2dummy*(.468)Q3dummy 

  
  Figure 74-11: LOGEST estimates trend and seasonality

  During the first quarter, the Q1 dummy equals 1 and the Q2 and Q3 dummies equal 0. (Recall that any number raised to the power 0 equals 1.) Thus, during a first quarter, we predict quarterly revenues to equal 4219.57*(1.0086)^{quarter number}*(.435).

  During a second quarter, the Q1 dummy and the Q3 dummy equal 0 and the Q2 dummy equals 1. During this quarter, we predict quarterly revenues to equal 4219.57*(1.0086)^{quarter number} *(.426). During a third quarter, the Q1 dummy and the Q2 dummy equal 0 and the Q3 dummy equals 1. We predict quarterly revenues during this quarter to equal 4219.57*(1.0086)^{quarter number}*(.468). Finally, during a fourth quarter, the Q1, Q2, and Q3 dummies equal 0. During this quarter, we predict quarterly revenues to equal 4219.57*(1.0086)^{quarter number}.

  In summary, we have estimated a quarterly upward trend in revenues of 0.9 percent (around 3.6 percent annually). After adjusting for trend, we find the following:

  • Quarter 1 revenues average 43.5 percent of Quarter 4 revenues.

  • Quarter 2 revenues average 42.6 percent of Quarter 4 revenues.

  • Quarter 3 revenues average 46.8 percent of Quarter 4 revenues.

  To create a seasonal index for each quarter, we give the omitted quarter (Quarter 4) a value of 1, and we find an average quarter has a weight equal to the following (see cell K2 in Figure 74-11):

  

  Then we can compute the relative seasonal index for Quarters 1–3 by copying from K4 to L4:M4 the formula K6/$K$2. The Quarter 4 seasonality is computed in cell M2 with the formula 1/K2. After adjusting for trend, we conclude that:

  • Quarter 1 sales are 80 percent of a typical quarter.

  • Quarter 2 sales are 73 percent of a typical quarter.

  • Quarter 3 sales are 75 percent of a typical quarter.

  • Quarter 4 sales are 172 percent of a typical quarter.

  Suppose we want to generate the forecast for each quarter corresponding to our fitted equation (Equation 1). We can use the Excel GROWTH function to create this forecast. The GROWTH function is an array function with the syntax GROWTH(known ys,known xs,new xs,True). This formula will give the predictions for the new xs when Equation 1 is fitted to the data contained in the ranges specified by known ys and known xs. Thus, selecting the range J6:J27 and array-entering in cell J6 the formula ={GROWTH(E6:E27, F6:I27,F6:I27,TRUE)} generates forecasts from Equation 1 for each quarter’s revenue. For example, our forecast for Quarter 4 of 1997 using Equation 1 is $4.366 billion.