Arrays and Array Formulas 0) Introduction: 1) Array Constants: 2) Array Formulas in Cells: 3) The FREQUENCY Array Function: 4) Array Formulas: Operators 5) Array Formulas: More Functions 6) Array Formulas: A Final Complex Example 7) Summary: 0) Introduction: In this lecture, we will discuss arrays, which are similar to ranges (they contain a sequence of values), but they do not HAVE TO appear in cells on the worksheet (although sometimes they do; they don't have to). We have seen many useful formulas that operate on ranges. Here we will learn that many of these same functions operate similarly on arrays, and we will learn new special functions that operate only on arrays. Generally, we use array formulas to reduce the number of extraneous cells needed to perform a calculation. Extraneous cells store temporary results that might obscure our worksheet: i.e., cells that we don't need to see but we do need to use to calculate other values that we are interested in seeing. Because array formuas are complicated, we often simplify them by defining names for large ranges/arrays used in the formulas. In this lecture the distinction between a RANGE OF CELLS (containing values) and an ARRAY (containing values) is critical. 1) Array Constants: Arrays come in three forms: column arrays (in one column), row arrays (in one row), and rectangle arrays (having at least 2 rows and/or 2 columns). We write array constants by entering all the array values inside {} braces, writing the upper-left hand value first and the lower-right hand value last, separating values on the same row (if any) by a comma (,) and values on different rows (if any) by a semi-colon. Let's see some examples. Our first example is a row array constant with 3 values {1,2,3}. Here the values are on the same row (but in different columns) so these values are separated by commas. Recall the ROWS and COLUMNS function: Here ROWS({1,2,3}) calculates 1 and COLUMNS({1,2,3}) calculates 3. We can also use the SUM function on an array constant, writing SUM({1,2,3}) which calculates 6, summing the three values in this row array. Our second example is a column array constant with 3 values {1;2;3}. Note the semicolons (instead of commas, as in the value above), which separate values in the same column (but in different rows). Here ROWS({1;2;3}) calculates 3 and COLUMNS({1;2;3}) calculates 1 (switched from the previous example). Again, we can write SUM({1;2;3}) which calculates 6, summing the 3 values in the column array. Our third example is a rectangular array constant with 9 values, separated into three rows and three columns. For purposes of illustration, I will first write this array as follows, emphasizing the structure of 3 rows and 3 columns. {1, 2, 3; So, we have three rows, with rows separated by a semicolon 4, 5, 6; and 3 values on each row, with columns separated by a comma 7, 8, 9}. Of course, we can (and should, since we write formulas on only one line) write this array constant much more compactly as {1,2,3;4,5,6;7,8,9}. Here ROWS({1,2,3;4,5,6;7,8,9}) and COLUMNS({1,2,3;4,5,6;7,8,9}) each calculate 3. Also, SUM({1,2,3;4,5,6;7,8,9}) calculates 45, which is just like summing the equivalent rectangular range of cells. We can put only values in an array constant, but values of any type: including numbers, text, and BOOLEAN values. But, we may NOT put any formulas or defined names. For example, we can write the rectangle array constant {"Arnie",90; "Barbara",83; "Charlie",84} which represents a rectangular array constant with 3 rows, each containing two columns (the first column is a name, the second a number). We can also define names that refer directly to array constants, illustrated in the example below for calculating grades, which is our first real example of using arrays for doing something practical. We have seen a few ways in Excel to calculate a grade based on a score. We will now see one more, probably the easiest, using LOCATE operating on two named arrays. First let's define the name Criteria1 to refer to the column array constant {0;60;70;80;90} and Grades to refer to the column array constant {"F";"D";"C";"B";"A"}. This specifies that 0 is the criteria for an "F", 60 is the criteria for a D, 70 is the criteria for a C, etc. In cell B3, we can calculate the grade for the value in green cell B1, by using this criteria, by entering the formula =LOOKUP(B1,Scores1,Grades). We can also define the name Criteria2 to refer to a row array constant, say {0,50,65,75,85} notice the comma separators for row arrays- that assigns grades using a slightly different criteria (cutoffs). Suppose that B2 had the number 1 or 2 in it (1 means use Criteria1, 2 means use Criteria2), The formula =LOOKUP(B1,CHOOSE(B2,Scores1,Scores2),Grades) appears in cell B2 to calculate the grade, using the selected score (B1) and the selected criteria (B2). -->Examine the Constant worksheet in the workbook for this lecture -->Examine the defined names Grades, Criteria1, and Criteria2, all defined with --> a scope of the Constant worksheet; examine the formula above in cells B3 --> and what it calculates from green cells B1 and B2 (try a score of 85, with --> criteria 1 (it is a B) and with criteria 2 (it is an A) -->Cells B5 and B6 calculate the number of rows and columm in Grades (which --> is a column array); change each to calculate their result on Criteria2 --> (which is a row array; finally calculate the rows and columns using the --> defined name TaxTable. So, whenever we can represent some information as an array constant, we can do so directly in a formula. If we are going to use the same array constant in many different formulas, or it is just long, we can name the constant and refer to it by name in whatever formulas it is used. 2) Array Formulas in Cells: Array formulas use array arguments and/or return array results. We saw one such function when computing grades: LOOKUP took a few array arguments, but returned just one text result. In this section we will explore functions that produce array results and how to store an array result in a range of cells: because arrays have multiple values, so we need multiple cells to store them. To start, we will put the array constants into a range of cells -->Examine the Constant worksheet in the workbook for this lecture -->Select cells B8:B12 -->Type the formula =Criteria1 (it will appear at the top and in cell B8) -->THE NEXT STEP IS CRITICAL -->Hold down the Ctrl and Shift keys, while pressing the Enter key once; notice --> that the 5 values from the column array that Criteria1 refers to are put -- in column B, in the 5 selected rows 8 through 12 -->Click any cell in the range B8:B12; notice that the formula {=Criteria1} --> appears in every cell: that is, the formula you typed with extra {} braces --> around it. Excel put these {} braces and is telling us that this range --> stores the values from the array formula that is named Criteria1 So, when entering a formula whose result is an array, we should 1) Select cells for the same size of array in the same orientation (row, column, or rectangular) 2) Enter the array formula in the upper-leftmost cell 3) Hold down the Ctrl and Shift keys, while pressing the Enter key once The formula in the selected range cells show the array formula inside {} braces. Once we have put an array formula in a range of cells, we cannot change an individual cell in that range. If we make changes (including deleting the formulas all together) we must select the ENTIRE RANGE and make changes to the ENTIRE RANGE. Let's try some examples. -->Try to put the value 0 inside cell B10: a pop-up message (the same kind we --> have seen for data validation) will prohibit the change; click OK the --> press the Esc, key to restore the cell's original valu -->Select cells B8:B12 and type the formula =Grades -->Press enter; notice that you get an error message; click OK -->Why an error? We didn't enter the array formula in all the cells, we just --> tried entering it in the first cell. Let's try again. -->Hold down the Ctrl and Shift keys, while pressing the Enter key once; notice --> the the 5 values (all letter grades) from the column array that Grades --> refers to are put in column B, in the 5 selected rows 8 through 12 -->Select cells B8:B12 and press the Del key; notice the formulas are deleted --> from all these cells and we can now put individual values into these cells So, we must remember to enter array formulas by holding down the Ctrl and Shift keys, while pressing the Enter key. And that once we create an array formula in a range of cells, we cannot change just one of those cells, we must change (or delete the formula from) all. What happens if we select a range of cells different from the size of the array specified by the array formula, or try to orient these values differently (e.g., try to put a column array on a row)? First let's see what happens if our selection has the right orientation but is too short. -->Select cells C8:C11 (just 4 cells, not all 5) -->Type the formula =Criteria1 (it will appear at the top and in cell C8) -->Hold down the Ctrl and Shift keys, while pressing the Enter key once; notice --> that the first 4 values from the column array that the name Criteria1 --> refers to are put in column C -->These 4 cells show the array formula {=Criteria1} So, it puts array values into the range from the array, but stops prematurely, when all the range cells are filled. Now, let's see what happens if our selection is too long. -->Select cells D8:D13 (6 cells, more than the 5 in the array constant) -->Type the formula =Criteria1 (it will appear at the top and in cell D8) -->Hold down the Ctrl and Shift keys, while pressing the Enter key once; notice --> that the 5 values from the column array that the name Criteria1 refers to --> are put in column D, in the 5 selected rows 8 through 12, followed by a --> 6th value #N/A = No value Avaiable -->These 6 cells show the array formula {=Criteria1} The same is true when we use select a row range and store a row array, like Criteria2, in the selection. -->Now, repeat this exercise with the row array that the name Criteria2 --> refers to by successively selecting the cells B15:F15, B16:E16, and --> B17:G17; notice that the result are simlar, but for row arrays not --> column arrays Now lets see what happens if we select a column range and try to put a row array into it. -->Select cells B19:B23 (5 cells in a column) -->Type the formula =Criteria2 (a name that refers to a row array) -->Hold down the Ctrl and Shift keys, while pressing the Enter key once; notice --> that the values from the first column in the column array appear in each --> of the selected cells. -->These 5 cells show the array formula {=Criteria2} So, Excel puts the value from the 1st column in each row position. We can use the TRANSPOSE array function to change a row array into a column array so we can store it into a column range (and vice versa) -->Select cells B25:B29 (5 cells in a column) -->Type the formula =TRANSPOSE(Criteria2) -->Hold down the Ctrl and Shift keys, while pressing the Enter key once; notice --> that the 5 values from the row array that Criteria2 refers are transposed --> into a column array and are put in column B. 3) The FREQUENCY Array Function: Histograms The FREQUENCY function is very useful for producing Histograms, which Excel can easily plot as a Column chart (the second most important chart type after XY Scatter). It calculates an array (based on some combination of ranges and arrays), which typically appears in a range of cells that are used as input to a Column chart. The form of the FREQUENCY array function is FREQUENCY(data,bins) where data and bins can be ranges or arrays. Data is the data that we want to do the frequency analysis of; Bins determines how the data is grouped. For example suppose we have a 100 point test and want to calculate the number of students who score at or below 9 points, between 10 and 19 points, between 20 and 29 points, ..., between 90 and 100 points; and over 100 points. This problem can be solved with the data that appears on the Histogram worksheet. The data to be analyzed is in the range B1:D18. We will examine three simple solutiions to this problem, which all use array formulas in different ways, but that produce the same Column chart. First, we use the bins in the range F4:F13. This range has 10 values (9, 19, ..., 100). The FREQUENCY function calculates an array containing 11 values: the last one is a bin for all values strictly greater than the last one, 100. Examine the cells in the range G4:G14; they contain the array formula {=FREQUENCY(B1:D18,F4:F13)} Second, we can write the FREQUENCY formula with the bins specified by an array. Examine the cells in the range H4:H14; they contain the array formula {=FREQUENCY(B1:D18,{9;19;29;39;49;59;69;79;89;100})}. With this approach we don't need to occupy cells on the worksheet with the bin values. Third, we can define a name to refer to the bins (see the name HistogramBins which refers to the array {9;19;29;39;49;59;69;79;89;100} and the name HistogramResults, which refers to the formula shown in the previous paragraph, but using the name HistogramBins =FREQUENCY(Histogram!$B$1:$D$18,HistogramBins) Note the absence of {} braces in this formula. With this approach we don't need to occupy cells on the worksheet with the bins or the frequencies -we need just the data; in fact, we could have even put the data as an array in the FREQUENCY function, but there is a lot of data being analyzed. Examine the cells in the range I4:I14; they contain the name HistogramResults that refers to the results produced by HistogramBins by the FREQUENCY formula. -->Examine the array formulas in columns G, H, and I The chart shown on the Histogram page uses the name HistogramResults in Series Values, and uses the name HistogramBins in the Horizontal (Category) Axis Labels. So, we could have dispensed with all the information in columns F through I, so this page could have only the data and chart on it. -->For example, the column labeled 89 show all scores bigger than the previous --> column label (79) up to 89, so it counts the number of scores 80-89 -->Delete all the information in colums F, G, H, and I; notice the histogram --> stays the same -->Select the data in the chart; select Series1; edit Legend Entries (Series) --> and examine what is in Series Values and Horizontal (Category) Axis --> labels (special to Column charts, not in XY Scatter charts). -->Change the Horizontal (Category) Axis labels by entering the column array --> constant {"F";"F";"F";"F";"F";"F";"D";"C";"B";"A"} As one quick sidenote, cell B1 contains the array formula SUM(HistogramResults) and calculates the 54, because there are 54 values in the histogram. Recall that the HistogramResults name refers to an array formula that produces an array containing 11 values (the 11 values appearing in columns G, H, and I). Calling the SUM function on this array adds-up all the values in the array (which is the same as adding up all the the values in the bins), and every score in the cell range B1:D18 appears in one bin. It is equivalent to calling SUM({0;0;0;0;4;8;9;13;10;10;0}). Finally, constructing a Data Table also involves array formulas (click on a cell in any data table to see how it appears, showing where in the worksheet the row and/or column inputs are entered before doing each calculation. 4) Array Formulas: Operators Excel extends the meanings of operators to arrays, allowing us to state complex calculation succinctly. As we saw in the previous section, we can use arrays to store data instead of cells, so we can use arrays and array formulas to reduce the information on a worksheet. Sometimes we will debug our formulas using extra cells, but then remove them while producing bigger formulas. Sometimes we need to apply the goldilocks principle: not use too few or too many cells on a worksheet, but just the right number. On the Operators work sheet the cell range A1:C6 store sales and price data for 5 common tools. We will see how to compute the revenue generated by each item, and the total revenue added up for all items, first using ranges and then using array formulas (a few different version). Examine cells E2, which contains a simple formula to compute the revenue (B2*C2) for Hammers, which is copied/filled down to cell E6. Also, Cell E8 calls SUM(E2:E6) which sums all the value in the range, producing the total revenue of all five items. So, we can produce the required result using ranges. Cell range F2:F6 contain one array formula that performs this same calculation, {=B2:B6*C2:C6}. When we ask Excel to multiply two ranges it creates an array for the result: the first position of the array contains the operator applied to the first position of each range; the second position of the array contains the operator applied to the second position of each range; etc. In this case the result is the array equivalent to {B2*C2;B3*C3;B4*C4;B5*C5;B6*C6} (BUT note that we cannot write an array constant this way because it has operators, but this is how the multiply operator works when applied to two ranges. We will see that other operators behave similarly, doing their operation on a two ranges (or arrays) of data, one datum at a time. As with cell E8, cell F8 calls SUM(D2:E6) which sums all the value in the range, producing the total revenue of all five items. By using one array formula, we can compute this sum without storing any information (the individual products) in cells. Cell G8 calls SUM(B2:B6*C2:C6), which directly sums the array produced by the multiplication, without storing the values from this array in any cells. Note that if you examine cell G8 it displays as {=SUM(B2:B6*C2:C6)} which means I typed the array formula =SUM(B2:B6*C2:C6) and held down the Ctrl and Shift keys, while pressing the Enter key. I'd like to abbreviate this operations as typing CSE. -->Use the Evaluate Formula icon to watch the results as Excel calculates the --> value of the array formula in cell G8. In contrast, if you examine cell H9 it displays as =SUM(B2:B6*C2:C6) which means I typed the array formula =SUM(B2:B6*C2:C6) and just pressed Enter. The value it calculates is #VALUE. Use the Evaluate Formula icon to watch the results as Excel calculates the value of this formula: when not entered in the cell as an array formula, B2:B6 evaluates to #VALUE, not an array containing the values from the range B2:B6. So, whether a formula is an array formula or not controls how ranges are evaluated inside the formula. Next, if you examine cell I* it displays as {=SUM(B2:B6*{10;5;15;6;4})} which replaces the second range by an array with the same values (and CSE was typed after the array formula =SUM(B2:B6*{10;5;15;6;4})was entered into this cell). So, Excel knows how to compute the produce of two range or the product of a range and an array; in fact, it can even compute the array product of two arrays and then sum the result in array: examine cell J9 which stores the array formula {=SUM({100;30;20;65;42}*{10;5;15;6;4})} Generally, when we call the SUM function directly on an array, for example SUM({1;2;3}) it calculates the sum of all the array values, in this case 6. We will see that other standard functions behave similarly. Finally, what happens if Excel performs an operator on two different sized ranges/arrays: it pads out the smaller range/array with #N/A values. Then any operation on that value will produce a #N/A result. Look at the array formula in K2:K6; it is {=B2:B6*C2:C7} where the second range is longer than the first by 1 value, so the first is padded to include a #N/A in the last position. Since I entered this array formula just in the 5 cells K2:K6, it took just the first 5 values, which were all legitimate values. Note though, the array formula in cell K8: {=SUM(B2:B6*C2:C7)} tries to add all the values in the array, including the the last value which is #N/A, so the result is #N/A I entered the same array formula from cells K2:K6 in cells L2:L7, which shows all 6 values in the array resulting from the padding and multiplication; note it shows the #N/A value explicitly in the 6th position, L7, which is why the array formula in cell K8 produces an error. We can also use operators between ranges/arrays and single values. For example we can compute 2*B2:B6. In this case Excel creates an array for the result: the first position of the array contains the operator applied to the value and the first position of the range; the second position of the array contains the operator applied to the value and the second position the range; etc. On the Operators worksheet, the result is the array {200;60;40;130;84}. In fact, we can add 1 to an array by writing {2;5;1;4} + 1 which results in the array {3;6;2;5}. We can square every value in a range/array, e.g., {1;2;3;4}^2 which results in the array {1;4;9;16}; and 2^{1;2;3;4} results in the array {2;4;8;16}: 2 raised to the 1st, 2nd, 3rd, and 4th power. So, we can use the standard arithmetic operators on pairs of ranges/arrays or on single values and a range/array. The result is always an array that is the size of the biggest range/array in the calculation. Relational Operators Producing BOOLEAN values and Selective Summing We can also use relational operators on ranges/arrays and on single values and a range/array to create an array of BOOLEAN values. This action will turn out to be very useful. The cell range A11:C15 store information showing employee names, their quotas, and how much they actually sold. Examine cells D11:D15 which contain the array formula {=C11:C15>=B11:B15} and produces the array {TRUE;TRUE;FALSE;TRUE;FALSE} stored in these 5 cells (TRUE for every person who sold at least their quota). Examine cell D17, which calculates the number of people who made their quota: =COUNTIF(D11:D15,TRUE). Although we have not discussed it before, if we us an arithmetic operator on a BOOLEAN value, Excel treats each BOOLEAN value like an equivalent integer, with FALSE having the equivalent integer value 0 and TRUE having the equivalent integer value 1. So, for example FALSE * 1 calculates 0 and TRUE * 1 calculates 1; likewise, FALSE + 0 calculates 0 and TRUE + 0 calculates 1. It is useful to be able to convert BOOLEAN values to 0 and 1 (and we often do so by multiplying by 1 or adding 0). Examine cells E11:E15 which contain the array formula {=1*(C11:C15>=B11:B15)} and produces the array {1;1;0;1;0} stored in these 5 cells (1 for every TRUE value and 0 for every FALSE value). The parentheses are critical here: if we wrote {=1*C11:C15>=B11:B15} leaving out these parentheses, Excel would first perform the multiplication, producing an array containing every value in the range C11:C15 multiplied by 1, and then perform the >= operator, which would result in an array of BOOLEAN values (the same as in D11:D15). Cells F11:F15 show the results of using this INCORRECT formula. Examine cell E17, which uses this data to calculate the number of people who made their quota in a different way: =SUM(E11:E15), which sums a 1 for each person who made quota and a 0 for each who didn't. In fact, cell E18 does this calculation without requiring any cells to store the BOOLEAN/(or 0 1 values), by just summing the array that results from the computation (not storing the array in any cells): {=SUM(1*(C11:C15>B11:B15))}. Examine cells H11:H15 which contain the array formula {=1*(C11:C15>1.2*B11:B15)} and produces the array {0;0;0;1;0} stored in these 5 cells (1 for every TRUE value and 0 for every FALSE value). These values indicate who sold over 120% of their quota. Cell H17 calculates how many people sold over 120% of their quota without requiring any cells to store the BOOLEAN/(or 0 1 values), by just summing the array that results from the computation (not storing the array in any cells): {=SUM(1*(C11:C15>1.2*B11:B15))}. As another example like this one, we can count how many differences there are between the numbers in cells B20:B27 and C20:C27. We can do this without arrays by computing in cells D20:D27 whether each pair is different, and then count in D29 how many TRUE values are in this column. Or, we can use an array formula to compute this value using no extra cells: see {=SUM(1*(B20:B27<>C20:C27))} in cell C29. -->Use the Evaluate Formula icon to watch the results as Excel calculates the --> value of the array formula in cell C32 We can use array formulas to sum numbers that have some specific property easily, using a similar approach. For example suppose that we enter values into green cells B31:B32 and what to add up all the numbers in range C31:C40 that are a between Min and Max. One way to do it is to put the formula =AND($B$31<=C31,C31<=$B$32) into cell D31 and then fill/copy it down to D40 (telling whether the number to its left is between Min and Max; then put the formula =IF(D31,C31,0) into cell E31 and then fill/copy it down to E40 (it uses an IF to put either C31 or 0 into its cell, based on whether C31 is TRUE); finally, cell E42 sums all the values in the range D31:D40. This approach requires 2 extra columns of cells to do the calculation. We can also use one complicated array formula to do the job. Cell C42 contains the array formula {=SUM(($B$31<=C31:C40)*(C31:C40<=$B$32)*C31:C40)} it multiplies a BOOLEAN array (whether each value in the range C31:C40 is greater than or equal to Min) times a BOOLEAN array (whether each value in the range C31:C40 is less than or equal to Max); that yields an array of 1s and 0s: 1s where both BOOLEANs are TRUE, 0s where at least one BOOLEAN is FALSE. Then we multiply this array time the range C31:C40: the resulting array either stores a value from C31:C40 (wherever there was a 1 calculated, that value was between Min and Max) or 0 (wherever ther was a 0 calculated). If we sum these values, as in the formula above, the result is the sum of all the numbers that have values between Min and Max. -->Use the Evaluate Formula icon to watch the results as Excel calculates the --> value of the array formula in cell C42 As a last example here, we can compute, without any extra cells, who sold the highest percentage of their quota. Here is how I went about solving the problem (building up the formula). First I wanted to use an array formula to find the maximum of the percentages of everyone's quota. So I wrote {=MAX($C$11:$C$15/$B$11:$B$15)} in cell J21 which calculates the maximum of the percentages (sold divided by quota). Then I wanted to find the index of the person who had that percentage, so I put the formula above in a bigger one. So I wrote {=MATCH( $J$21, $C$11:$C$15/$B$11:$B$15, 0 )} in cell J22 which matches the value from J21 against all the percentages (which are unordered, so I used 0 as the match-type). It tells me its the 4th position. Finally I use this value as an index in the names list A11:A14. So I wrote =INDEX(A11:A15,$J$22) in cell J23 Note that this doesn't need to be an array formula. But, I wanted to solve this problem with no extra cells, but I used J21 and J22 to store intermediate results. I could have put all these formulas together into one huge array formula that calculates its result directly from the data. So I wrote (note this is an array formula) {=INDEX($A$11:$A$15, MATCH( MAX($C$11:$C$15/$B$11:$B$15), $C$11:$C$15/$B$11:$B$15, 0))} in cell J25, which calculates everything in that one cell, but is very hard to understand (even if we have seen the parts built-up). So maybe we should keep the one formula goal, but define some useful names instead. So I defined the names Names refers to $A$11:$A$15 Sales refers to $C$11:$C$15 Quotas refers to $B$11:$B$15 Percents refers to Sales/Quotas On the Operators worksheet, and defined the following final array formula {=INDEX(Names, MATCH( MAX(Percents), Percents, 0))} in cell J26 Which calls the same functions INDEX, MATCH, and MAX as above, but does so on names that refer to ranges, not the ranges themselves. So we can use array formulas to reduce the number of cells needed to perform a calculation, often along with defining some useful names. 5) Array Formulas: More Functions We have already seen that we can use the SUM function on an array, and it acts like the SUM function on ranges. The other statistical functions: AVERAGE, COUNT (there are a few variants), MIN, MAX, SMALL, and LARGE, also work on arrays. For example, SMALL({0;2;4;5;8;9},3) calculates the 3rd smallest value in the array {0;2;4;5;8;9}, which is 4. The same result is calculate even if we change the order of the values in the array: {5;4;0;8;2;9} Surprisingly, the RANK function DOES NOT work with arrays in both arguments; it works only with ranges or a first argument that is an array and a second argument that is a range. I read the documentation and cannot figure out why the others work completely with arrays and RANK doesn't, but I couldn't get any simple examples to work with two arrays. Like SUM, we can call the AND or OR functions with an array of BOOLEAN values (or a range of BOOLEAN values that Excel converts into an array). For example calling {AND({TRUE;FALSE;TRUE})} calculates FALSE (one FALSE in an AND means the calculated result is FALSE). We will find an interesting use for call AND on arrays below. So, if a function takes one range argument, we can typically -but not always- call it on an array argument too. The result is an array (of the same number of values), with each value in the calculated array representing that function called on the values in the original array. So, we can call ={SUM(ABS({1;2;-4;3;2;-1))} and it calculate the result 13 as follows SUM(ABS({1;2;-4;-3;2;-1})) calculates to (calls ABS on each value) SUM({1;2;4;3;2;1})) calculates to 13 In the first step, the ABS function calculates an array of 6 values, each value is the absolute value of one of the array values; in the second step, the SUM function calculates the sum of these absolute values. -->Use the Function evaluation tool on cell B1 Let's now combine the AND function and the results of calling the ISNUMBER function on array. We want to know (calculate a BOOLEAN value) whether every value in the array is a number. We can call AND(ISNUMBER({1;2;"Nope";4})) calculates to (calls ISNUMBER on each value) AND({TRUE;TRUE;FALSE;TRUE)) calculates FALSE -->Use the Function evaluation tool on cell B2 If we wrote {=AND(ISNUMBER(A1:A20))} Excel would call the ISNUMBER function on the range and return an array result, and then AND would calculate whether all of the values in those 4 cells were numbers. -->Use the Function evaluation tool on cell B3 We can also compute the number of cells in this range that are numbers using the COUNT function directly, =COUNT(E1:E4) or by using array formulas: either {=COUNT(ISNUMBER(E1:E4),TRUE)} or even {=SUM(1*ISNUMBER(E1:E4))} On the Operators worksheet, we can use a function similar to the one in cell J26 to determine which number in the range C11:C15 is closest to a specified number (in green cell H28). We do so by computing the ABS of the differences between the number and all the numbers in the range, and then matching to find the index of the minimum difference, and indexing the original range to find that number. The formula (using the defined names) is in cell H29. {=INDEX(Sales, MATCH(MIN(ABS(Sales-H28)),ABS(Sales-H28),0))} A slight variant in cell H30 replaces the first argument in INDEX (Sales) by Names, to produce the name of the person who has the sales closest to the specified amount. -->On the Operators worksheet, enter a number in the green cell H28 and watch --> cells H29 and H30 compute the closest sales and the person who made that --> sale. In case two people are closest, the first one in the range A11:A15 --> will be the result of the calculation. -->Use the Function evaluation tool on cell H29 and H30 Before we get to the last big example in this section, we will take a short detour to examine how to use function that will be very useful to us. The function =ROW(1:10) calculates the array {1;2;3;4;5;6;7;8;9;10} and the function =COLUMN(1:10) calculates the array {1,2,3,4,5,6,7,8,9,10}. Suppose that the name Data refers to some range, then the formula =ROW(INDIRECT("1:"&ROWS(Data))) calcuates an array with all the values from 1 up to the number of rows in the range Data refers to; we can think of these as all the indexes for this data, and will use such a formula to solve the problem below. Some Excel functions also allow us to replace a single argument with an array. For example, the form of the SMALL function is SMALL(range or array,ordinal), where the ordinal 1 means first smallest, ordinal 2 means second smallest, etc. In this function we can specify an array (of ordinals) for the second argument, andthe SMALL function will produce an array as follows. To be concrete we will examine the array formula {=SMALL({1;8;5;12;3;7},{1;2;3})} which returns a 3 value array (because there is an array of 3 ordinals) storing the 1st smallest, 2nd smallest, and 3rd smallest values: {1;3;5}. It it like Excel is computing { SMALL({1;8;5;12;3;7},1); SMALL({1;8;5;12;3;7},2); SMALL({1;8;5;12;3;7},3) } So, we can use this function easily to compute the sum of the three smallest values in some range named R as =SUM( SMALL(R,{1;2;3}) ). Another function that works in an interesting way with ranges is the IF function. In the first extended form, we call it as IF(array/range of BOOLEAN, value1, value2) This function produces an array that is the same size the array/range, which contains a combination of value1 and value2: the result array contains value1 where array/range of BOOLEAN is TRUE and value1 where array/range of BOOLEAN is FALSE. So, for example {=IF({TRUE;TRUE;FALSE;TRUE}, 5, 10)} calcluates the array {5;5;10;5} In fact, {=IF({1;1;0;1}, 5, 10)} calcluates the same array, {5;5;10;5} where Excel uses 1 for TRUE and 0 for FALSE. As we saw in the example with SMALL, we can subsitute arrays/ranges of values for value1 and value2, and write IF(array/range of BOOLEAN, array/range 1, array/range 2) Let's assume that each array/range is the same size. This function produces a very interesting result: an array that contains a combination of values from array/range 1 and array/range 2, with the array/range of BOOLEAN controlling from which of these two arrays/ranges the values are coming. For example, the array formula {=IF({1;1;0;1}, {1;3;5;7}, {2;4;6;8})} calcluates the array {1;3;6;7}, getting the 1st, 2nd, and 4th values from {1;3;5;7} and the 3rd from {2;4;6;8}, because the 1st, 2nd, and 4th boolean are equivalent to TRUE and the 3rd is equivalent to FALSE. 6) Array Formulas: A Final Complex Example On the Functions worksheet, we define the name Data to refer to the cell range B11:B30, 20 cells containing values. We also define the name DataIndexes to be =ROW(INDIRECT("1:"&ROWS(Data))), an array storing the values 1 up to 20. The cell range C11:C30 stores the array formula {=DataIndexes} so that we can see these values. In this section we will eventual create an array that contains all the values from Data that are positive. Using these names, we can enter the array formula {=SMALL(Data,DataIndexes)} in cell range D11:D30 which sorts the values in cells B11:B30 by calculating an array with the 1st smallest in B11:B30, the 2nd smallest in B11:B30, ... the 20th smallest (really the largest) in B11:B30. Cell range E11:E30 contains the array formula {=IF(Data>0,Data,0)} which produces an array storing 20 values: storing every value in E that is positive, with all negative values replaced by 0. Aside, another quick example: if this range contained error values, we could sum all the non-error values by using the array formula {=SUM( IF(ISERROR(Data), 0, Data) )} which sums an array with 0 substituted for any error value (so we can sum these values without affecting the sum). Continuing with the main example, cell range F11:F30 contains the indexes of all the values that are positive (with "" for all indexes of non-positive numbers). Missing indexes are 1, 7, 10, 11, 16, and 17 (indexing negative values in Data). The actual array formula is {=IF(Data>0,DataIndexes,"")} Cell range G11:G30 contains the indexes of the positive Data numbers, but all at the start of the array. The array formula is {=SMALL(F11:F30,DataIndexes)} which puts the smallest of the indexes first in the array. The error values #NUM! appear at the bottom of the array, because there are only 14 numbers in the range F11:F30; so, if we try to find the 15th smallest number in this array, the answer is the #NUM! error. Cell range H11:H30 contains the values (not the indexes) of the positive Data numbers at the start of the array. The actual array formula is {=INDEX(Data,G11:G30)} which uses the indexes in G11:G30 to index the original data, leaving the same error values at the bottom. Cell range I11:I30 contains the values of the positive Data with the error values replaced by "". The actual array formula is {=IF(ISERROR(H11:H30),"",H11:H30)} (see the ASIDE above, which is similar in function to this formula) Finally, J11:J30 contains this same answer, but not using any extra cells. It requires that we define the name Temp3 to refer to the array formula =INDEX(Functions!Data,SMALL(IF(Functions!Data>0,Functions!DataIndexes,""), Functions!DataIndexes)) which composes the function in columns F,G, and H. Then we define the name Final to refer to the array formula =IF(ISERROR(Functions!Temp3),"",Functions!Temp3) which removes the error values from Temp3. Although chose Functions!Data>0 for those values to keep, we could use any predicate (way to calculate TRUE if value should be included, FALSE if not) to decide which values to keep. For example, if we substituted Functions!Data>AVERAGE(Functions!Data) for Functions!Data>0 then column J would contain only those values > than the average (the average of all the values in column B is about 20.25), so column J should show the numbers 41, 61, 82, 94, 38, and 61. I'm not sure how well you understand this comnplex task. It is beyond what you really need to understand for this course, but it shows how powerful Excel is in the hands of someone who has lots of experience with this material and can build up complicated calculations. ------------------------------------------------------------------------------ 7) Summary: Here is a quick summary of skills to acquire from working on this lecture. You should be able to discuss each of these topics a bit, but more importantly know HOW TO DO something in Excel, Know how to use array formulas to reduce the number of extraneous cells (that might obscure our worksheet) needed to perform a calculation.* The downside of using array formulas is that they are often large and difficult to debug and get correct. One in-between approach is to build up such formulas gradually, using extra cells (as we did on the Operations worksheet in cells J21 through J23, and then either put those parts together into one formula (e.g., J25) or define some names to help simplify the formula (J26).