Basic Reference Functions 1) Introduction 2) The INDIRECT function: Converting Text to a Range 3) The ADDRESS, ROW, and COLUMN functions: 4) ROWS, COLUMNS, and CHOOSE: -- 5) The INDEX and MATCH functions: 6) The LOOKUP function: 7) 3 Quick Statistical Functions: RANK, SMALL, and LARGE 8) The OFFSET function: 9) Summary: 1) Introduction In this lecture we will examine the most useful functions from the Lookup and Reference group. Some of these functions were needed to complete the Retirement, Tennis, and Secretary models. The LOOKUP function, which often appears in Excel models, was used on the Interest+Chart worksheet in Lab #3 -in cell N8- and on the Rabbits+Foxes worksheet in Lab #4 -in cells I16 and I17. These functions are simple (when we eventually understand them) but powerful. Important to some of these functions is the difference between a "range" and "text that describes a range". If we write A1:A10 inside an Excel formula, it reprsents a range: so we can write the formula =SUM(A1:A10) in a cell to compute the sum of all the values in that range. But, if we write "A1:A10" in an Excel formula, it respresents a text value. What makes this difference even more confusing is that if we write just A1:A10 in a cell (note no = sign before it to signify a formula) that cell is said to contain the text "A1:A10" (but it displays without the double quotes). So, there is NO DIFFERENCE in entering A1:A10 into a cell or entering ="A1:A10" into a cell. But there IS A DIFFERENCE when these two are written inside formulas. For example, =SUM(A1:A10) sums the values in the range A1:A10. But =SUM("A1:A10") calculates the value #VALUE! because one cannot apply the SUM function to text; it must be applied to a range of cells. In this lecture we will examine functions that convert text to a range, and functions that convert a range to text -and functions that convert ranges to numbers and numbers to ranges. Using such functions, we can perform more genral and powerful calculations in Excel than without these functions. The INDEX/MATCH/LOOKUP functions finds the index of a value in a range; or, given a range and an index, determine the value there. Finally, when we learn about arrays in Excel, we will revisit many of these functions to see new but similar forms that are based on arrays instead of ranges. In all the functions below, we specify rows before columns. This is a bit strange, because in Excel we specify an address, like B9, by first listing the column letter followed by the row number. 2) The INDIRECT function: Converting Text to a Range The INDIRECT function takes one argument that evaluates to some TEXT that refers to a cell or range. That is, a cell stores text that refers to other cells; i.e.,, it refers to other cells indirectly, via the text. Before continuing, quickly look at the Indirect worksheet toggling the Show Formulas icon. On the Indirect+ARC worksheet, cell A1 contains the text "B1". Cell B1 contains the value 3 (and C1 contains the value 5, and D1 contains 7). Cell B2 contains the formula =INDIRECT(A1) (A2 is a just a label with this text in it, to remind you what is in B2), which evaluates to =INDIRECT("B1"), which evaluates to =B1, which evaluates to 3. -->Use Excel's Evaluate Formula tool to watch it evaluate the formula in B2. -->Enter the text C1 into the green cell A1; notice that B2 now evaluates to 5 -->Use the Excel's Evaluate Formula tool to watch it evaluate the formula in B2 -->Enter the text D1 into the green cell A1; notice that B2 now evaluates to 7 -->Enter the text G1 into the green cell A1; notice that B2 now evaluates to --> "Text for Range", the text value stored in G1 (notice it is not bold- --> faced, because no VALUE are bold-faced: bold-facing is a format applied --> to a cell -->Enter the text XYZ into the green cell A1; notice that B2 now evaluates to --> #REF!, an error meaning an illegal reference was found in the formula, --> because there is no cell referenced as XYZ We have previous seen how to construct text from text and values with the & (catenate) operator; for example, we often use such a cell to specify the name of a data series in a legend by putting a formula like ="Angle ="&A1 in the cell, and then using a reference to that cell in the top Data Series box. If cell A1 contains the value 20, then the value of the formula is the text "Angle = 20". We can call the INDIRECT function on more interesting text ranges built in this way. On the Indirect+ARC worksheet cells F1:F10 contain random values. Cell H1 contains the formula ="F1:F"&H2 which specifies text that describe a range of cells in the column F, between 1 and whatever value is in H2. So, if H2 stores 3, the value of this formula is "F1:F3". Cell H3 stores =MAX(INDIRECT(H1)), a formula that calculates the maximum value in the range specifed by H1. It evaluates to =MAX(INDIRECT("F1:F3")), which evaluates to =MAX($F$1:$F$3), which evaluates to .60102. Thus indirect converts a range specified by text into an absolute range that MAX can use to calculate its value. Note that the formula =MAX(H1) will calculate 0 (because while H1 is the address of some cell, the value in H1 is not a number); also, =MAX("H1") calculates #VALUE!, an error because the argument to MAX is text (not a number or range of numbers, so it is not a legal cell). This is a very picky difference, but Excel does treat these two cases differently. -->Enter the value 4 into the green cell H2; notice that cell H1 changes to --> "F1:F4" and cell H3 changes to 0.96838, because that is the maximum --> value in the range F1:F4 -->Enter the value 10 into the green cell H2; notice that cell H1 changes to --> "F1:F10" and cell H3 changes to 0.96909, because that is the maximum --> value in the range F1:F10 (that value is in cell F9) -->Enter the text XYZ into the green cell H2; notice that cell H1 changes to --> "F1:FXYZ" and cell H3 changes to #REF!, an error meaning an illegal --> reference was found in the formula, Finally, we don't even need cell H1 to calculate the range as text; we can do it by writing the larger formula =MAX(INDIRECT("F1:F"&H2)) which calculates the correct text range and then calls the INDIRECT function on it and then calls MAX on the result. -->Examine cell H4, which is =MAX(INDIRECT("F1:F"&H2)) -->Use the Excel's Evaluate Formula tool to watch it evaluate the formula in H4 Is it better to use cell H1 to calculate the text range, and then use H1 in a formula (as done in H3) or to calculate this text range directly (as done in H4)? For beginners, it is probably more useful to do things the first way: entering formulas into cells that store and show "temporary" information (the text range). Then if we make a mistake, we will SEE it as an incorrect value in that cell, and we will know to fix it. More advanced users would not use such a temporary cell, but if they make a mistake, it will be more difficult to see and debug the formula. Of course, using the Evaluate Formula tool helps, because it also allows us to SEE how the formula evaluates Given the INDIRECT function, when we write references to cells or ranges in formulas, we do not have have to know the exact cells/ranges beforehand. Instead, we can calculate them in another cell (as text) and then use the INDIRECT to convert the text into actual cells/range references that we can use in our formulas. Note that we used INDIRECT so help us solve the Secretary problem, computing the minimum rank of the number of secretaries that we pre-interviewed: the secretaries are in column C starting at C6, and the number to pre-interview is in B1. The resulting formula, in cell B2 is =MIN( INDIRECT( "C6:C"&(B1+5) ) ) -->Use the Excel's Evaluate Formula tool to watch it evaluate the formula in B2 3) The ADDRESS, ROW, and COLUMN functions: The ADDRESS function converts a row number (its first argument) and a column number (its second argument) into text that specifies a reference. For example, ADDRESS(2,3) calculates the text value "$C$2" because it specifies the second row (2) and the third column (C). This is a bit confusing because the ADDRESS function specifies the row (a number) first but when we write addresses we specify the column (a letter) first. If we omit the third argument, or specify it as 1, the reference is purely absolute; if the third argument is 4, the reference is purely relative; if it is 2-3 it is mixed. For example ADDRESS(2,3) or ADDRESS(2,3,1) calculates "$C$2" ADDRESS(2,3,2) calculates "C$2" ADDRESS(2,3,2) calculates "$C2" ADDRESS(2,3,4) calculates "C2" We often use the text result produced by the ADDRESS function as an argument in the INDIRECT function. The ROW/COLUMN functions calculate the integer row/column of a reference: ROW(C2) evaluates to 2; COLUMN(C3) evaluates 3, because C2 is the cell in the third column and second row. Mathematically we can state some identities using ROW, COLUMN, ADDRESS, and INDIRECT, tying all these functions together. First, given any reference, say A2 ADDRESS(ROW(A2),COLUMN(A2),4) evaluates to "A2" Second, if x and y are positive integers, ROW (INDIRECT(ADDRESS(x,y))) evaluates to x COLUMN(INDIRECT(ADDRESS(x,y))) evaluates to y These formulas are in cells B4, B5, and B6. -->Use Excel's Evaluate Formula tool to watch it evaluate the formulas in B4, --> B5, and B6 Using these functions, we can solve a problem related to the one solved in the section on the INDIRECT function. In the related problem, we want to calculate the maximum of some part of a row of values (see A12:J12). The major difficultly is that when the columns in this row vary, they are not represented by increasing NUMBERS, but by increasing LETTERS: A, B, C, ... Our solution will also call the INDIRECT function. On the Indirect+ARC worksheet cells A12:J12 contain the same random numbers as in cells F1:F10. ---------- Quick aside: Here is how I copied the column of cells F1:F10 into the row of cells A12:J12 The key word is "transpose" -->Select the column F1:F10 -->Press ctrl/c or click the Copy icon in the Clipboard section on the Home tab -->Click on the cell you want to be the leftmost cell in the row (use A20 now) -->Click the disclosre triangle below the Past icon in the Clipboard section -->Click Paste Special; notice the pop-up window named Paste Special --> You can use the radio buttons here to copy various aspects of cells -->For our purpose here, click the Transpose check box (bottom right) and OK This mechanism allows you to easily copy columns to rows or rows to columns ---------- Cell A13 contains the text "A12", the start cell of the array. Cell A14 contains the formula =ADDRESS(ROW(INDIRECT(B13)),COLUMN(INDIRECT(B13))+B15-1,4) which ultimately specifies text that describe the last cell in the row 12 that the MAX function will examine. Let's assume B15 stores 3 (if it doesn't, enter 3 in the green cell B15). -->Use Excel's Evaluate Formula tool to watch it evaluate the formulas in A14 Here is my description of how the formula is evaluated. INDIRECT(B13) evalutes to the cell A12 (the cell B13 stores as text). ROW(A12) evalutes to 12 INDIRECT(B13) again evalutes to the cell A12 (the cell B13 stores as text). COLUMN(A12) evalutes to 1 (A is the first column). This value of 1 is added to the value is cell B15 (3) and 1 subtracted, leading to the value 3 (which means the last value in this row to be examined is the 3rd). ADDRESS(12,3,4) evaluates to the text "C12" (row 12, column C: the third one), which is the value appearing in cell B14 Finally, cell B16 stores =MAX(INDIRECT(B13&":"&B14)) which catenates "A12" (the text value in cell B13) to the ":" to "C12" (the text value in cell B14) to create the text "A12:C12" which INDIRECT turns into a range tht MAX calculates its value from. -->Use Excel's Evaluate Formula tool to watch it evaluate the formulas in B16 Again, this formula calculates the maximum value in the range specifed by B13 (which stores "A12"), catenated with the text ":", catenated with B14 (which stores "C12"), so it evaluates to =MAX(INDIRECT("A12:C12")), which evaluates to =MAX($A$12:$C$12), which evaluates to .60102. Thus indirect converts its text into an absolute range that MAX calculates its value from. -->Enter the value 4 into the green cell B15; notice that cell B14 changes to --> "D12" and cell B16 changes to 0.96838, because that is the maximum --> value in the range A12:D12. -->Enter the value 10 into the green cell B15; notice that cell B14 changes to --> "J12" and cell B16 changes to 0.96909, because that is the maximum --> value in the range A12:J12 (that value is in cell I12) As before, we could omit the "temporary" formula in cell B14 and write the formula in cell B16 as =MAX(INDIRECT(B13&":"&ADDRESS(ROW(INDIRECT(B13)),COLUMN(INDIRECT(B13))+B15-1,4)) which is very long, complicated, and difficult to debug if we get it wrong. It is better to use a cell like B14 to hold the "temporary" result, which we can see and better debug if it is wrong. A final form of the ROW and COLUMN functions have no arguments, and are written just ROW() and COLUMN(). They return the row or column value of the cell in which these functions occur. The cells in B8:C9 all contain the formula ="(" & ROW() & "," & COLUMN() &")" and display "(8,2)", "(8,3)", "(9,2)", "(9,3)" respectively -->Copy and paste B8 into other locations on the worksheet and observe how the --> text value it produces changes 4) ROWS, COLUMNS, and CHOOSE: These three functions are fairly simple in what they do, so we will learn them together here. Notice in the last section we saw function ROW and COLUMN; the functions we discuss here are ROWS and COLUMNS: note the S on the end. The ROWS function takes a range and calculates the number of rows in that range; likewise, the COLUMNS function takes a range and calculates the number of columns in that range. For example, the range A1:C12 includes the rows 1 to 12 and the columns A to C. So, ROWS(A1:C12) evaluates to 15; COLUMNS(A1:C12) evaluates to 3 ROWS(A1) and ROWS(A1:C1) both evaluate to 1 -->Examine the formulas in cell B16 and B18; B18 contains the formula --> =COLUMNS(INDIRECT(B13&":"&B14)) which builds text representing the rows --> that the MAX function is called on in cell B16, and calls INDIRECT to turn --> that text into a reference, and finally calls COLUMNS, which evaluates to --> the number of columns in the reference, which is the same as the number --> of values being summed; so it should always match green cell B15 -->Change the value in cell B15 and observe the change in cell B18 If we needed to calculate the number of cells in any range, say A10:J25, we could do it by the formula =ROWS(A10:J25)*COLUMNS(A10:J25). If this range was stored as text in cell X10, "A10:J25", we can calculate the number of cells in the range by =ROWS(INDIRECT(X10)) * COLUMNS(INDIRECT(X10)) The form of the CHOOSE function is CHOOSE(selector, value1, value2, ...., up to value254) The selector can be anythng that evalutes to a number: a constant, a cell reference, or some function (if the number is NOT AN INTEGER, it is truncated to the integer lower than it: both 5.1 and 5.8 are truncated to the integer 5). The values can evaluate to anything. If selector evaluates to 1, then the CHOOSE function evaluates to value1; if selector evaluates to 2, then the CHOOSE function evaluates to value2; ... As a simple example, involving only constants, here is a shorter way to compute a grade from a percentage. The cell B22 contains the formula =IF(B20<60,"F", CHOOSE(B20/10-5,"D","C","B","A","A")) If the grade is less than 60 (a special case, not using the CHOOSE function), the IF function evaluates to "F". Otherwise IF uses the CHOOSE function, first dividing the final percentage by 10, so any D will be a number 6.xxx, any C will be the number 7.xxx, any B will be the number 8.xxx, any A will be the number 9.xxx or maybe 10 (if the final percentage is 100). By subtracting 5 we end up with Ds 1.xxx, Cs 2.xxx, Bs 4.xxxx, As 4.xxx or 5. So the choices are "D" for 1.xxx (which is truncated to 1), "C" for 2.xxx (which is truncated to 2), "B" for 3.xxx (which is truncated to 3), and "A" for 4.xxx (which is truncated to 4) and also for 5 (which is why there are two "A" grades at then end: one for a selector of 4 and one for a slector of 5. We need two "A"s because for a 99 the selectore is 99/10-5 which truncates to 4, while 100/10-5 truncates to 5. -->Put various final percentages into the green cell B21; notice the result --> computed in B22 -->Excel's Evaluate Formula tool to watch it evaluate the formula in cell B22 For the next example of using CHOOSE, rows 25-27 show the names (in column A) and 3 test scores (in columns B-D) for 3 students. Cell B30 contains the formula =CHOOSE(B29, A25, A26, A27) which chooses, selected by the avlue in the green cell B29, one of the three names in A25:A27 Cell B31 contains the formula =SUM(CHOOSE(B29, B25:E25, B26:E26, B27:E27)) which chooses a range, selected by cell B29, to sum. For example, if the value in cell B29 is 2, then in cell B31 =SUM(CHOOSE(B29, B25:E25, B26:E26, B27:E27)) evaluates to =SUM(CHOOSE( 2, B25:E25 ,B26:E26, B27:E27)) (selector is 1)evaluates to =SUM($B$26:$E$26) evaluates to 273 -->Put various numbers (1-3) into the green cell B29; notice the result --> computed in B30 and B31 -->Click on cell B30 or B31 and -->Use Excel's Evaluate Formula tool to watch it evaluate the formulas in B30 --> and B31 Finally, put the value -1, 0, and 4 in green cell B29; notice that with a selector that is out of range (the selections correspond only to values 1, 2, and 3) this function computes #VALUE! as an error value. I have added a small scroll bar (linked to B29) that allows the user to scroll through the names and sums without having to re-enter values into B29. We will use some of these functions while describing the following reference functions. 5) The INDEX and MATCH functions: The INDEX and MATCH function consume and produce opposite information: the INDEX function mostly tells you a value on a worksheet, if you tell it where to find the value; the MATCH function tells where to find the value on the worksheet, if you tell it what value to find. The INDEX function is given a range of cells, and row and column indexes, and evaluates to the specified cell or range (that is a row or colunm; not a rectangular region). Let's look first at the case where INDEX evaluates to just one cell. The general form of the INDEX function is =INDEX(range, row-index, column-index) Read the table below as follows: in the rectangular range C10:E13, if you select row/col index 1,1 the function evaluates to whatever is stored in cell C10. If you select row/col index 3,2 the function evaluates to whatever is stored in cell D12. | C | D | E | ---+--------------------+--------------------+--------------------+ 10 | INDEX(C10:E13,1,1) | INDEX(C10:E13,1,2) | INDEX(C10:E13,1,2) | ---+--------------------+--------------------+--------------------+ 11 | INDEX(C10:E13,2,1) | INDEX(C10:E13,2,2) | INDEX(C10:E13,2,2) | ---+--------------------+--------------------+--------------------+ 12 | INDEX(C10:E13,3,1) | INDEX(C10:E13,3,2) | INDEX(C10:E13,3,2) | ---+--------------------+--------------------+--------------------+ 13 | INDEX(C10:E13,4,1) | INDEX(C10:E13,4,2) | INDEX(C10:E13,4,2) | ---+--------------------+--------------------+--------------------+ The INDEX function is like a 2-dimensional CHOOSE function: we tell it which row to select from, and which column to select from (where the first row and first column are each at index 1), and it choses the value there. On the IM+L worksheet, the formulas in cells B3:B5 operate on the range G5:J9. We can enter a row and column selection in the green cells B1 and B2. That cell will be highlighed in the G5:J9 cell range in orange (using conditional formating: select this range, click the Conditional Formating icon, and then click Manage Rules to see how I did this). The cell B3 contains the following formula =INDEX(G5:J9, B1, B2) which uses the values in cells B1 and B2 to index the range G5:J9. We can illustrate this function on the spreadsheet. -->Enter a few legal row values (1-5) and a few legal column values (1-4) in --> the green cells B1 and B2; notice the movement of the orange fill in that --> range; the value of cell B3 should be the same as the orange highlighed --> value in the range -->Examine the Conditional Formatting for all cells in the range G5:J9. The --> formula is =AND(ROW()-4 = $B$1, COLUMN()-6 = $B$2) -->It checks whether the cell's ROW() and COLUMN() are the same as B1-4 and --> B2-6 (because F4, the one marked "IMPORTANT", the cell above the rows --> and to the left of the columns, is the 4th row and the 6th column). --> That is the cell we start indexing from. -->Only one cell will evaluate this boolean formula as TRUE, and that cell will --> be highlighted orange. -->Enter a row value outside 1-5 and/or a column value outside 1-4; notice no --> cell is filled, and cell B3 displays #REF! error Note that a purely row range (say A10:K10) has many columns (A through K) in just one row (10); a purely column range (C1:C10) has many rows (1 through 10) in just one column. So when indexing a column range, the row index must be 1 (or it can be omitted), and when indexing a column range, the column index must be 1 (or it can be omitted). So, if I wanted the 5th cell in the range A10:K10 we could write the formula =INDEX(A10:K10,,5) which is the same as =INDEX(A10:K10,1,5) because the range contains just one row. We can also use INDEX to choose not just one cell, but a row or column of cells. This is useful when using a function like SUM, AVERAGE, etc. So, if we wanted the 5th column in the range A1:K10 we could write the formula =INDEX(A1:K10,,5), which refers to the range E1:E10. If we wanted the 5th row in the range A1:K10 we could write the formula =INDEX(A1:K10,5,), which refers to the range A5:K5. This feature is illustrated in cells B4 and B5, which contain the functions =AVERAGE(INDEX(G5:J9,B1, )) =AVERAGE(INDEX(G5:J9, ,B2)) Both of these functions leave out one argument, so they select all columns in a row (the first form) or all rows in a column (the second form). In the top formula, INDEX returns a range consisiting of all column values in the row whose value is in B1. The formula computes the average of all those values (ignoring the non-numeric fruit name). You can verify that these sums are correct by looking at the row sums in column L (in cells L6:L9). You can also use Evaluate Formula to wee how this formula evaluates. In the bottom formula, INDEX returns a range consisiting of all row values in the column whose values is in B2. The formula computes the average of all those values (ignoring the non-numeric month name). You can verify these sums are correct by looking at the column sums in row 11 (H11:J11). -->Enter a few legal row values (2-5) and a few legal column values (2-4) in --> the green cells B1 and B2; notice the values computed in cells B4 and B5 -->Enter the value 1 in the green cell B1 and/or B2; notice the cell --> displaying #DIV/0! error, because row 1 contains month names and column --> 1 contains fruit names (so the AVERAGE finds 0 numbers so the denominator --> when computing the average is 0 -->Enter a row value outside 1-5 and/or a column value outside 1-4; notice the --> cell displaying the #REF! error Now onto the MATCH function. Generally, it looks up a value in a row range or column range of cells, and tells you the index of that value: 1 for first in the range, 2 for second, etc. Its form is =MATCH(match-value, range: row or column only, [match-type]) where [match-type] means match-type is optional. The simplest match-type is 0, which means look for the first EXACT match (equality). With this type, the values in the range can be in any order. Green cells B15 and B16 allow us to enter a match value, and calculate the MATCH functions in B17, B18, and B19 which try to find the match-value in column range D15:D24, E15:E24, and F15:F24 respectively. Notice column range C15:C24 shows the indexes of each row: index 1 for row 15, index 2 for row 16, etc. -->Enter a 0 in green cell B16, for an exact match -->Enter a 15 in green cell B15; notice the values in B17 (index 6), B18 --> (index 3), and B19 (index 8); the index cells turn yellow and the matched --> cells turn orange -->Enter other values in green cell B15 (in these ranges); notice the values --> calculated in B17, B18, and B19 -->Enter a value not in these ranges in green cell B15 (for example 22); notice --> the value in B17, B18, and B19 is #N/A Let's examine an interesting relationship between the INDEX and MATCH functions,which are somewhat opposites. If we use the index calculated by the MATCH function in the INDEX function, we get back the match-value. =INDEX(col-range, MATCH(match-value,col-range,0), 1) evaluates to match-value =INDEX(row-range, 1, MATCH(match-value,row-range,0)) evaluates to match-value MATCH tells us the index of match-value; if we use that index in the INDEX function with the same range, we get the match-value. Of course, if match-value is not in the range, the result is #N/A. Examine cell B21, which calculates the following function on the column D15:D24 =INDEX(D12:D21, MATCH(B15, D15:D24, 0), 1) -->Enter a value into the green cell B15; notice the same value will appear in --> cell B21, if B15's values is in the column range D15:D24; if not cell B21 --> will be #N/A The two other possible match-types are 1: finds largest value <= match-value (range must be in ascending order) -1: finds smallest value >= match-value (range must be in descending order) Cells B18 and B19 contain the MATCH function working with range E15:E24 (sorted ascending) and F15:F24 (sorted descending). Now we can enter a value into B15 and enter a match-type (0, 1, or -1) into B16 and see the result of the MATCH functions in B17:B19. I have conditionally formated these cells to show in red if it is not appropriate to use that match-type with the function that cell contains (because some ranges aren't sorted the right way. -->Enter a 1 in green cell 16, for matching the largest value <= match-value --> which works only for the formula in cell B18 (cells B17 and B19 turn red) -->Enter a 15 in green cell B15; notice the value in B18 (index 3) -->Enter a 16 in green cell B15; notice the value in B18 (index 3) because 15, --> the value at index 3 in E15:E24, is the largest value <= 16 -->Enter a 30 in green cell B15; notice the value in B18 (index 3) because 15, --> the value at index 3 in E15:E24, is still the largest value <= 30 -->Enter a 31 in green cell B15; notice the value in B18 (index 4) because 31, --> the value at index 4 in E15:E24, is now the largest value <= 31 -->Enter other values here and watch the result in B18 -->Enter a -1 in green cell B13, for matching the smallest value >= match-value --> which works only for the formula in cell B19 (cells B17 and B18 turn red) -->Enter various values in green cell B15 to see how the function in cell B19 --> works Another way to think about the +1 match type is it keeps going until it finds a value bigger than the one it is matching (or goes beyond the range of values to match), and then goes back one. The +1 and-1 match-types may seem useless, but here is a real example where this type of matching is just what is needed. Suppose that we put the US tax tables (for a Single Filer) in cells K15:M20. We interpret this to mean, if you earn 0 to $8374 you pay 10% tax; if you earn $8375 to $33,999 you pay 15% tax; etc.; if you make $373,650 or over you pay 35% tax. Now, given a certain income entered in green cell I14, we need to be able to compute the tax rate for that income (done in cells I16 and I17, in two slightly different ways). The first thing that we must do is to calculate the right index for that income. Cell I15 contains the formula =MATCH(I14, K15:K20, 1) which uses match-type 1 on the Low Income column (K15:20). For example, if we enter 35,000 in green cell I14, the MATCH function calculates the index 3, because 34,000 is the largest value <= 35,000. We can use this index with the INDEX function to compute the Tax Rate for this amount. That is, index 3 in column M is the tax rate for that income from column K. Cell I16 contains the formula =INDEX(M15:M20, I15, 1) Which uses the value calculated in I15 as a row index in the Tax Rate column (M15:M20), also specifying a column index of 1 (there is only one column). Cell I17 calculates this value without the "temporary" cell I15. =INDEX(M15:M20, MATCH(I14, K15:K20, 1), 1) So, we can use the INDEX and MATCH functions together for this purpose, with the special match-type of 1 not requiring an exact match, but more of a match of values in a range. 6) The LOOKUP function: The LOOKUP function is a more convenient way to do what we typically do with a combination of the INDEX/MATCH functions. We cannot always use LOOKUP, but we often can, and when we can it is simpler than using INDEX/MATCH combinations. IMPORTANT: To use the LOOKUP function, the range of values MUST BE ORDERED from smallest to largest. If the range isn't ordered, most likely you will use a combination of the INDEX/MATCH functions to solve the problem. LOOKUP allows us to lookup a value in one column (or row) and returns a result "associated" with that value from another column (or row): as the same index. It always uses match-type 1: finding the index of the largest value that is <= match-value (and the range must be in ascending order). We will start by examining the general form of LOOKUP on ranges, written as LOOKUP(match-value, lookup-range, result-range) For example, also on the IM+L worksheet, the cell I19 contains the formula =LOOKUP(I14, K15:K20, M15:M20) Which also computes the Tax Rate. Whatever index it finds in the first range, it uses the same index in the second range to calculate its result. Compare this function to the equivalent formula in cell I17 =INDEX(M15:M20, MATCH(I14, K15:K20, 1), 1) which uses a combination of INDEX/MATCH. LOOKUP is certainly simpler and more direct. THe LOOKUP function is used on the Interest+Chart worksheet in Lab #3 -in cell N8- and on the Rabbits+Foxes worksheet in Lab #4 -in cells I16 and I17. As a final example, we will use the LOOKUP function to calculate a student grade based on his/her score (a problem we've already seen done a few ways: with a nested IF function and the CHOOSE function). Cells B28:B32 specify the grades; to the left of each score (in cells A28:A32) is the first (lowest) score for which that grade is given. We can enter in green cell B34 the score to compute the grade of (or use the scrollbar to specify the score). In cell B35 is the function =LOOKUP(B34, A28:A32, B28:B32) So the score in B34 is looked-up in A28:A32; the value of the function is the value at the same index in the column to its right, B28:B32. For example, if we store 75 into B34, the LOOKUP function finds 70 as the largest number <= 75, (at index 3) so the LOOKUP function returns the grade at index 3 (to its right, on the same row in in column B). Finally, we have not covered the reference functions HLOOKUP, VLOOKUP, and OFFSET in this lecture. HLOOKUP and VLOOKUP are variants of LOOKUP (and not as important) and OFFSET, which we will discuss later in this lecture, is a more general version of LOOKUP (which we can use to simplify other, more complicated, INDEX/MATCH combinations). 7) 3 Quick Statistical Functions: RANK, SMALL, and LARGE Here is a short description of three related, useful, and simple statistical functions. First is RANK, whose general form is RANK(value,range,[order]) It returns the rank of value among all the values in range, according to the order (if [order] is 0 or omitted, it returns a rank 1 for biggest value; if [order] is 1, it returns a rank 1 for smallest value). If value is not in the range, the result in #N/A. On the RSLO worksheet -->Enter a value in green cell B1, for ranking; notice cells B2 and B3 compute --> its rank (B2 where the highest value has rank 1; B3 where the lowest value --> has rank 1). Notice what happens when you enter a value no in the range --> F2:F11 Also, notice that the cells in the G and H columns are =RANK(F2, $F$2:$F$11, 0) and =RANK(F2, $F$2:$F$11, 1) So row 2 in G and H shows the ranks (computed both ways) of value 60; row 3 shows the ranks of value 24; etc. Note that for computing ranks in a column of values RANK(value, range, 0) = ROWS(range) - RANK(value, range, 1) + 1 Second and third are SMALL and LARGE, whose general form is SMALL(range,ordinal) and LARGE(range,ordinal) Note that ordinal 1 means first, ordinal 2 means second, etc. It returns the ordinal smallest/largest specified value in range. So SMALL(range,1)/ LARGE(range,1) returns the first smallest/largest value in range; SMALL(range,2)/LARGE(range,2) returns the second smallest/largest value in range; etc. -->Enter a value in green cell B5; notice cells B6 and B7 compute its the B5th --> smallest and largest values. Notice what happens when you enter a value --> bigger than the number of values in the range (e.g., enter 15 in B5) Also, notice that the cells in the I and J columns are =SMALL($F$2:$F$11, E2) and =LARGE($F$2:$F$11, E2) With the column E storing 1, 2, etc. So row 2 (using Index 1) shows the 1st smallest and largest values in columns I and J; row 3 (using Index 2) shows the 2nd smallest and largest values in columns I and J. RANK and SMALL/LARGE are inverses: RANK computes an ordinal from a value, and SMALL/LARGE compute a value from an ordinal. So, if we use the same range, LARGE(range, RANK(value,range) ) = value SMALL(range, RANK(value,range,1)) = value whenever value is in the range. So, columns I and J show the values from F2:F11 sorted lowest to highest and highest to lowest respectively, We'll do more with sorting (a few different ways) later. 8) The OFFSET function: The OFFSET function generalize INDEX: given a starting cell, INDEX can select either a cell or a range that is a row or column; given a starting cell, OFFSET allows us to specify where the real starting cell is (by adding row and column offset) and can select either a cell or an arbitrary range (row, column, or rectangular region). It has five arguments: a reference cell (the anchor cell in the range) and four integer values that say which cells to include, based on the anchor. The general form of the OFFSET function is OFFSET(reference-cell, row offset, column offset, height, width) Suppose cell specifies the cell at row R and column C. The resulting range is from: row R+rows and column C+cols to: row R+rows+height-1 and column C+cols+width-1 So, the range starts at (R,C) shifted by rows and cols; it ends height-1 rows below and width-1 columns to the right. Graphically, we can picture this as +--------------+ |reference-cell| +--------------+ | row offset | | v column offset width +--------------+ +--------------+--------------+--------------+ | |------>| | | | +--------------+ +--------------+--------------+--------------+ | | | | +--------------+--------------+--------------+ | | | | height +--------------+--------------+--------------+ | | | | +--------------+--------------+--------------+ | | | | +--------------+--------------+--------------+ | | | | +--------------+--------------+--------------+ On the RSLO worksheet -->Enter a value in green cells B14:B18 for specifying the five parameters to --> the OFFSET function (B14 is a cell, e.g. F16, and the others are all small --> integers); notice the range specified by the offset (its text appears in --> cell B23), and all the cells in the range appear filled in orange, and --> cell B19 shows the sum off the values in the range specified by the --> OFFSET function -->Experiment with zero and small negative values in Rows and Columns (normally --> these values are non-negative, but they are allowed to be negative). In --> fact, Rows and Columns are often 0: Height and Width often supply all --> the important information -->The values in Height and Width should be 1 or greater (if not, the entered --> value will have a fill of red). The OFFSET function -the final one is this lecture- is like the INDIRECT function -the first one in this lecture: both produce ranges from their arguments. In fact, OFFSET(cell,r,c,h,w) produces a range we can also specify using the INDIRECT, ADDRESS, ROW, and COLUMN functions: INDIRECT(ADDRESS(ROW(cell)+r,COLUMN(cell)+c) &":"& ADDRESS(ROW(cell)+r+h-1,COLUMN(cell)+c+w-1) which is the formula (without the INDIRECT, so we can see this range as text) in cell B21. So, we could do without the OFFSET function, but then we would have to write much more complicated formulas. 9) 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 the meaning of and how to use the following functions (here listed alphabetically) showing their arguments and returned result. Here range/cell means either a range or cell. Look up and see the definitions of these functions in Excel's help. If you need a refresher for these functions while you are working in Excel, be comfortable using help. ADDRESS integers text COLUMN cell integer COLUMNS range/cell integer INDEX range,1-2 integers cell/range* INDIRECT text range/cell LOOKUP range*,value value LARGE range*,integer value MATCH range*,value integer OFFSET refernce-cell,4 integers range/cell RANK value,range[,integer] integer ROW cell integer ROWS range/cell integer SMALL range*,integer value Above, Range* is a range that is a row or column, not a rectangle Recognize the power of building text that specifies a range, and converting that text into a range with INDIRECT. Understand how to lookup information via the INDEX/MATCH functions, and when posible and simpler, the LOOKUP function.