Updated: Spring 2020 Basic Reference Functions 1) Introduction 2) The INDIRECT function: Converting Text into Cell/Range Addresses 3) Quick Text Function Review: &, LEN, FIND, LEFT, RIGHT, MID 4) The ADDRESS, ROW, and COLUMN functions: 5) ROWS, COLUMNS, and CHOOSE: -- 6) The INDEX and MATCH functions: 7) The LOOKUP and VLOOKUP functions: 8) 3 Quick Statistical Functions: RANK, SMALL, and LARGE 9) The OFFSET function: 10) Summary: -------------------------------------------------------------------------------- 1) Introduction In this lecture we will examine the most useful functions from the Reference and Lookup group. Some of the Reference 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. The Reference functions allow us to convert between cells, ranges, ints, and text. In this way we can calculate cells and ranges based on the values in other cells and ranges by converting the them to text, then calculating with the text (e.g. the & operator and text functions), then converting them back to cells and ranges. 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 represents 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 this text 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 (one cannot write SUM("ABC") either); it must be applied to a RANGE of cells.. When asked to use the Evaluate Formula tool, carefully look whether the operand of an operator or argument of a function is text (in quotes) or not. In the first part of this lecture, we will examine functions that convert text to a cell or range, and then functions that convert a cell or range to text; later we will examine and use functions that convert ranges to numbers and numbers to ranges. Using such functions, we can write formulas that perform more general and powerful calculations in Excel; things we cannot do without these functions. Later we will study the INDEX/MATCH/LOOKUP functions: INDEX, given a range and an index (1st, 2nd, 3rd), determines that particular value in the range; MATCH finds the index of a value in a range (is it the 1st, 2nd, 3rd, ...). Finally, when we (soon) learn about arrays in Excel, we will revisit many of these functions to see new but similar forms of them, which opertor 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 a cell address, like B9, by first listing the column letter followed by the row number. So, be careful. -------------------------------------------------------------------------------- 2) The INDIRECT function: Converting Text to Cell/Range Addresses "Indirection is the right direction" - Andrew Glew The INDIRECT function takes one argument that is (or evaluates to) some TEXT. That text must specify a cell or range; the value produced by the INDIRECT function is the actual cell or range the text specifies. Example 1: Here is a trivial example that will serve as a warmup. Assume cell B1 stores the values 3 and cell B2 stores the formula =INDIRECT("B1"). INDIRECT evalutes its argument, the text "B1", and INDIRECT returns the cell equivalent B1. So, it is as if cell B2 stores the formula =B1, which would store B1's value (3) into cell B2. Of course the INDIRECT function here is superfluous, we could have written cell B2 more simply as =B1. See the picture below. Here =X | Y means that formula X in a cell evaluates to values Y. A B +---------------------+---------------------+ 1 | | 3 | +---------------------+---------------------+ 2 | | =INDIRECT("B1") | 3 | +---------------------+---------------------+ Example 2: Now let us try a more complicated example. Assume that cell A1 stores the text "B1" (to store this text we could type B1 in that cell, or ="B1", or 'B1: all appear in the cell as just B1). Assume again that cell B1 stores the values 3 but cell B2 now stores the formula =INDIRECT(A1). INDIRECT evalutes its argument, the cell A1, which stores the text "B1", and INDIRECT returns the cell B1. So, again, it is as if cell B2 stores the formula =B1, which would store B1's value (3) into cell B2. See the diagram below. A B +---------------------+---------------------+ 1 | B1 | 3 | +---------------------+---------------------+ 2 | | =INDIRECT(A1) | 3 | +---------------------+---------------------+ Example 3: Now assume that C1 stores 5 and that cell A1 instead stores the text "C1". Assume again cell B2 now stores the formula =INDIRECT(A1). INDIRECT evalutes its argument, the cell A1, which stores the text "C1", and INDIRECT returns the cell C1. So, again, it is as if cell B2 stores the formula =C1, which would store C1's value (5) into cell B2. See the diagram below. A B C +---------------------+---------------------+---------------------+ 1 | C1 | 3 | 5 | +---------------------+---------------------+---------------------+ 2 | | =INDIRECT(A1) | 5 | | +---------------------+---------------------+---------------------+ Example 2 above appears on the Indirect+ARC worksheet. Toggle showing the formulas/values in cells to observe cells B2. -->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 is bold-faced: bold-facing is a format applied --> to a cell; it has been applied to G1 but not B1 -->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 previously 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". Example 4: As a final example assume cell C2 stores 7 and cell A1 number 1. Now assume B2 now stores the formula =SUM( INDIRECT("C"&A1&":"&"C"&(A1+1)) ). INDIRECT evalutes its argument to be "C1:C2", and INDIRECT returns the range C1:C2. So, it is as if cell B2 stores the formula =SUM(B1:B2), which would store the value 12 into cell B2. See the diagram below. A B C +---------------------+---------------------+---------------------+ 1 | 1 | 3 | 5 | +---------------------+---------------------+---------------------+ 2 | |(formula above) | 12 | 7 | +---------------------+---------------------+---------------------+ We can call the INDIRECT function on more interesting text ranges built in this way. On the Indirect+ARC worksheet cells F1:F10 contain the values of random numbers. Cell H1 contains the formula ="F1:F"&H2 which specifies text that describes a range of cells in the column F, between 1 and whatever value is in H2; we can set cell H2 directly or by moving the scrollbar. So, if H2 stores 3, the value of this formula is "F1:F3" (which displays without the quotes!). 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 text that specifies a relative range into an actual 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, so it uses 0 for the value of the non-number); also, =MAX("H1") calculates #VALUE!, an error because the argument to MAX is not a number, cell, or range of cells: it is just text. This is a very picky difference, but Excel does treat these two cases differently. Of course =MAX(INDIRECT("H1")) would again be 0. -->In the examples below, you put a value in cell H2 or adjust its scrollbar -->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; cell H5 changes to 2.391762, the sum of the --> values in 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); the cell H5 changes --> to 5.018957 the sum of the values in F1:F10 -->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 so by writing in cell H4 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. Likewise in cell H6 we can write the larger formula =SUM(INDIRECT("F1:F"&H2)) -->Examine cell H4 and H6 -->Use the Excel's Evaluate Formula tool to watch how it evaluates each formula Is it better to use cell H1 to calculate the text range, and then use H1 in a formula (as done in H3 and H5) or is it better to calculate this text range directly (as done in H4 and H6)? 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 (or in the formula itself) and then use the INDIRECT function to convert the text into an actual cell/range reference that we can use in our formulas. Note that we used INDIRECT to 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) ) ) -->In the Secretary worksheet, use the Excel's Evaluate Formula tool to watch -->it evaluate this formula in B2 Indirection is a powerful feature that is also quite useful in complicated worksheets, but it take a while to understand and use easily (but is worth the effort). -------------------------------------------------------------------------------- 3) Quick Text Function Review: &, LEN, FIND, LEFT, RIGHT, MID With our renewed interest in text, for use in the INDIRECT function (above), now is a good time to review some of the text processing functions we have already studied in Excel (see Lecture 2). We can now use the & operator and these functions to construct complicated text values that can represent cell references, and then use them in the the INDIRECT function. You can easily learn/practice these functions by entering them into cells. LEN(text): LEN("abcde") = 5 Calculates the number of characters in the text; its argument must evaluate to text (be text or a cell that contains text). Note the algebraic equality LEN(text1&text2) = LEN(text1) + LEN(text2) FIND(find_text,within_text,[start]): FIND("Bo","boBBoaaBo") = 4 Calculates the first index in within_text (the first index is 1) where the find_text occurs. The notation [start] means start is optional; if it is in included, it must be positive integer specifying the index where finding starts. So FIND(x,y, start) >= start. FIND("Bo","boBBoaaBo",6) = 8, because it starts finding at index 8 (the first "a"). If the find_text does not occur in the within_text (at start index or beyond) this function calculates the value #VALUE!; we will soon see how to detect such a value: ISERROR(#VALUE!) calculates TRUE; we often write =IF(ISERROR(...),...) LEFT(text,count): LEFT("abcde",1) = "a", LEFT("abcde",3) = "abc" Calculates the count left-most characters in the text. if count is 0 the calculate text is empty ("") and if count > LEN(text) then the calculated text is just text (all of text) RIGHT(text,count): RIGHT("abcde",1) = "e", RIGHT("abcde",3) = "cde" Calculates the count right-most characters in the text. if count is 0 the calculate text is empty ("") and if count > LEN(text) then the calculated text is just text (all of text) MID(text,start_index,count); MID("abcdefg",3,4) = "cdef" Calculates the count characters in the text, starting at start_index. Here count must be >=0 and start_index must be positive. If count is 0 the function calculates "" and if there aren't count characters in text starting at start_index it calculates all characters from start_index. MID means characters chosen from the middle of text. Often we use combinations of LEFT/RIGHT/MID with a count/start_index that is calculated by LENGTH/FIND. For example, If I wanted to get the text starting at the character x and going until 2 before the end of the text, we could write =MID(text,FIND("x",text), LEN(text)-1-FIND("x",text)). -->Enter the following formula into any cell on the worksheet, and use the --> Evaluate Formula tool to see how it works; it returns xcd -->=MID("abxcdef",FIND("x","abxcdef"), LEN("abxcdef")-1-FIND("x","abxcdef")) Notice this formula requires multiple uses of FIND("x","abxcdef"); we might compute this value once in a temporary cell and reuse that cell multiple times in such a formula....or not. See the discussion above about using temporary cells. Other useful text functions you might want to examine in the help system are REPLACE and SUBSTITUE. -------------------------------------------------------------------------------- 4) 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 actuall addresses like C2 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,3) calculates "$C2" ADDRESS(2,3,4) calculates "C2" So ADDRESS uses number to specify text; then, we often use the text result produced by the ADDRESS function (or two ADDRESS functions specifying the first and second cell of a range) as an argument in the INDIRECT function: concatenating the two ADDRESS texts with the ":" text between them. 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 B4 ADDRESS(ROW(B4),COLUMN(B4),4) evaluates to "B4" Second, if x and y are positive integers, ROW (INDIRECT(ADDRESS(x,y))) evaluates to x COLUMN(INDIRECT(ADDRESS(x,y))) evaluates to y ROW (INDIRECT(ADDRESS(4,2))) = ROW (INDIRECT("B4")) = ROW ("B4") = 4 COLUMN(INDIRECT(ADDRESS(4,2))) = COLUMN(INDIRECT("B4")) = COLUMN("B4") = 2 -->These formulas are in cells B4, B5, and B6 of the Indirect+ARC worksheet -->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 difficulty 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 A33 now) -->Click the disclosure triangle below the Paste icon in the Clipboard section -->Click 3rd/Last Icon on the 2nd row; hovering over it shows "Transpose (T)" This mechanism allows you to easily copy columns to rows or rows to columns, so matter how a sequence of values is formatted, you can transpose it. It works for rectangular selections as well. ---------- Cell B13 contains the text "A12", the start cell of the array. Cell B14 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. It uses the value in B15 to compute the number of values in the range to look at (ismilar to how cells H1 and H2 are related for the column F1:F10). Let's assume B15 stores 3 (if it doesn't, enter 3 in the green cell B15 or move the scrollbar appropriately). -->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 one big formula =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 same 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 -------------------- Some truth about Cell Addresses: Excel supports two styles of specifying a cell: they are called A1 and R1C1. A1 style is the default in Excel and what we are used to. We specify a cell by its column letter followed by its row number: eg., D3. To switch to R1C1 style: File | Option s | Formula | Working with Formulas and then check the R1C1 Reference style. You will see the column letters now replaced by numbers. So both rows and columns are now numbered. In the R1C1 style, we specify the cell that used to be D3 (in A1 style) by R3C4, because D3 is the 3rd row and the 4th column. When it comes to copying/pasting R3C4 is actually similar to $D$3: it is an absolute cell. We can make relative cells by using brackets: R[-1]C[3] means a cell 1 row up and 3 columns to the right. So if R is followed by an int/bracketed int the row is absolute/relative; if C is followed by an int/bracketed int the column is absolute/relative. writing no number/bracket after an R or C is like writing [0] after R or C. You can change any workbook from A1 to R1C1 or back. Excel will automatically change the style of the formulas: use formula auditing to see what it does. The ADDRESS function has a 4th parameter that is optional; if missing or TRUE it means a cell is specified in the A1 style; if FALSE the R1C1 style. Put FALSE as a fourth argument in B4 and see how the cell address changes. There are various places where the R1C1 style makes more sense to use, but for now we (and throughout most of the rest of the course) we will continue using the A1 style. One mystery about cell copying can now be addressed. If we are in R1C1 style, and a cell has a formula specifing RrCc, and that cell is copied m rows down and n columns to the right, the cell's formula will compute with the cell Rr+mCc+n (where up/left copying would specify negative values for m and n) -------------------- -------------------------------------------------------------------------------- 5) 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 12; COLUMNS(A1:C12) evaluates to 3 ROWS(A1) and ROWS(A1:C1) both evaluate to 1 ROWS(A:A) evaluates to 1,048,576 (the maximum number of rows in a spreadsheet) Columns(1:1) evaluates to 16,384 (the maximum number of columns in a spreadsheet) -->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 (only if the final percentage is 100). By subtracting 5 we end up with Ds 1.xxx, Cs 2.xxx, Bs 3.xxxx, As 5.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 selector of 5. We need two "A"s because for a 99 the selector 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 value in the green cell B29, one of the three names in A25:A27. The choose function can actually choose from a sequence of RANGES (not just values). For example, cell B31 contains the formula =SUM(CHOOSE(B29, B25:E25, B26:E26, B27:E27)) which chooses a range, selected by cell B29, and SUMs it. 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 2)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 Note that we could use INDIRECT and ADDRESS to construct the actual cells or ranges needed here, to write very general formulas 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. -------------------------------------------------------------------------------- 6) The INDEX and MATCH functions: The INDEX and MATCH functions 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. 6a) INDEX The INDEX function is given a range of cells, and row and column indexes; it calculates one cell's value or to a special range (on which we can call range fuctions like SUM). This special range must be either one row or one column, but not a rectangular region. Let's look first at the case where INDEX calculates to just the value of one cell. The general form of the INDEX function is =INDEX(range, row-index, column-index) As with the ADDRESS function, the row comes before the column. Read the table below as follows: in the rectangular range C10:E13, if you select row/col indexes 1,1 the function calculates whatever is stored in cell C10 (1st row in the range, 1st column in the range). If you select row/col indexes 3,2 the function evaluates to whatever is stored in cell D12 (3rd row in the range, 2nd column in the range). | C | D | E | ---+--------------------+--------------------+--------------------+ 10 | INDEX(C10:E13,1,1) | INDEX(C10:E13,1,2) | INDEX(C10:E13,1,3) | ---+--------------------+--------------------+--------------------+ 11 | INDEX(C10:E13,2,1) | INDEX(C10:E13,2,2) | INDEX(C10:E13,2,3) | ---+--------------------+--------------------+--------------------+ 12 | INDEX(C10:E13,3,1) | INDEX(C10:E13,3,2) | INDEX(C10:E13,3,3) | ---+--------------------+--------------------+--------------------+ 13 | INDEX(C10:E13,4,1) | INDEX(C10:E13,4,2) | INDEX(C10:E13,4,3) | ---+--------------------+--------------------+--------------------+ 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 in the range each have index 1), and it chooses the value there. On the IM+L worksheet, the formulas in cells B3:B5 operate on the range G5:J9 (double click any of these cells to see that range). 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, selecting the cell we want from the row specified in B1 and column specified in B2. We can illustrate this function on the spreadsheet. Note that INDEX(G5:J9,1,1) calculates the value in cell G5. -->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 highlighted --> 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 cell 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. Maybe it is easier to understand --> the formula if it is written =AND(ROW() = $B$1+4, COLUMN() = $B$2+6) -->All cells in this range have the same formula to turn them orange, but only --> one cell will evaluate this boolean formula as TRUE, filing its cell will --> to be highlighted in orange -->Enter a row value outside 1-5 and/or a column value outside 1-4; notice no --> cell is filled orange, 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 0 (or it can be omitted), and when indexing a column range, the column index must be 0 (or it can be omitted): but the commas must remain. 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,0,5) because the range contains just one row. It is counter-intuitive, but by leaving out the value specifying the row, it means "use ALL values in the row", as will be illustrated further below. We can also use INDEX to choose not just one cell, but a single 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 (cell B4), INDEX returns a range consisting 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 averages are correct by looking at the row averages in column L (in cells L6:L9). You can also use Evaluate Formula to see how this formula evaluates. If we wrote =AVERAGE(INDEX(G5:J9,B1,1)) where B1 is 3 and we use 1 to specify the column, the INDEX(...) evaluates just to 1 cell G7, which has no average because it is non-numeric (text). In the bottom formula (cell B5), INDEX returns a range consisting 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 We could have specified ther range H6:J9 in an INDEX function, which includes only the numeric values inside the table, not the Fruits and Month texts.Then we could use 1-4 as rows and 1-3 columns, which might be more natural 6b) MATCH Now onto the MATCH function. Generally, it finds/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] (match-type in square brackets) means match-type is optional. See the FIND function above, whose start was also 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 inverses. 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 back. 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(D15:D24, 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 index of largest value <= match-value (range in ascending order) -1: finds index of smallest value >= match-value (range must descending order) These result in Excel using APPROXIMATE matching rules, illustrated below. Cells B18 and B19 contain the MATCH function working with range E15:E24 (sorted ascending) and F15:F24 (sorted descending) respectively. 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 for the last value). -->Enter a 1 in green cell B16, 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) because 15, --> the value at index 3 in E15:E24, is the largest value <= 15 -->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 $8,374 you pay 10% tax; if you earn $8,375 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 (on column M) and MATCH (on column K) functions together for this purpose, with the special match-type of 1 not requiring an exact match (which would often fail here), but more of a match of values in a range. -------------------------------------------------------------------------------- 7) The LOOKUP and VLOOKUP functions: 7a) LOOKUP The LOOKUP function is a more convenient way to do what we typically do with a combination of the INDEX/MATCH functions (what we just did in the example above). We cannot always use LOOKUP, but we often can, and when we can it is simpler than using INDEX/MATCH combinations. If we cannot use LOOKUP, often a special combination of INDEX/MATCH will work. So, for problems like these, try to solve them using a simple LOOKUP first; if that fails, fall back and try the more complex but general INDEXT/MATCH functions.. Generally, we have two columns (or rows) and lookup a value in one column (or row) and retrieve the value at the same index in the other column (or row). IMPORTANT: LOOKUP always uses APPROXIMATE matching rules, specifically +1 (as seen in the MATCH functions> So, to use the LOOKUP function, the range of values MUST BE STRICTLY ASCENDING. If the values aren't ordered in this way, most likely you will use a combination of the INDEX/MATCH functions (with match-type 0 or -1) 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): at the same index. It always uses match-type 1: finding the index of the largest value that is <= match-value (so the range must be in ascending order for LOOKUP to work). Often data is sorted in ascending order (and we will learn how to sort cells with a single command when we discuss databases). 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) This formula also computes the Tax Rate (its value should be the same as in cells I16 and I17, which use INDEX/MATCH). 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 (discussed at the end of the last section). =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 nested IF functions and the CHOOSE function). Cells B28:B32 specify the grades as a table of two adjacent columns: 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). 7b) VLOOKUP The VLOOKUP function is a variant of the LOOKUP function, but is slightly more general (it can use approximate matching, like LOOKUP and MATCH; but it can also use exact matching, only like MATCH: the advantage of exact matching is that the values don't need to be in ascending order, which makes it usable in more models). VLOOKUP also uses a slightly different argument structure. The general form is VLOOKUP(match-value, lookup-table, result-column,[approximate-matching]) To start, let's assume that we want to mimic LOOKUP and its approximate matching. We can either discard the approximate-matching argument or specify it as TRUE. For example, also on the IM+L worksheet, the cell I20 contains the VLOOKUP version of he LOOKUP formula in cell I19. =VLOOKUP(I14, K15:M20, 3, TRUE) Again, green cell I14 has the match-value, but now we specify a lookup-table that includes the income ranges (columns K and L) and the filer rate (column M). The match-value is always searched for in the first column of the lookup-table: column K. If it is found, the 3 argument specifies that the result of VLOOKUP is the value in the same row but the 3rd column of the table (column M). Note, the last argument TRUE means to use approximate-matching. That is the default, so this formula can be written equivalently as just. =VLOOKUP(I14, K15:M20, 3) Examine cell B36 which is the VLOOKUP equivalent of cell B35 which computes a grade; it omits the approximate-matching argument. Now we discuss the other possibility. If we specify the approximate-match argument as FALSE, it will search for the first value in first column in lookup-table, which exactly matches match-value (and these values in this column don't have to be any ordered). For example, the formulas in cells I32 and I33 use the table in range I26:K29 to find the Quantity and Cost@100 of the fruit specified in the green cell I31. Notice that these fruits do not appear in any special order. I32 is =VLOOKUP(I31, I26:K29, 2, FALSE) The value is I31 is searched for in column I (starting at row 26) and if an exactly is found, the value in that row and the 2nd column of the lookup-table is the result of the VLOOKUP. Note there is no equivalent in VLOOKUP of MATCH's -1 match-type (approximate matching of values in descending order). Also, VLOOKUP only works when the column to return is to the right of the column to search in the lookup-table: not always the case, but you might be able to change the lookup-table format for this purpose. The HLOOKUP function is similar to VLOOKUP, except that it searches for the match-value in the first row of the lookup-table and gets its result from the same column in the specified row. Finally, we will discuss the OFFSET function at the end of this lecture. It is a more general version of INDEX (which itself is complicated, but we can use it to simplify other, even more complicated, INDEX/MATCH combinations). First we look at some interesting statistical functions on ranges. -------------------------------------------------------------------------------- 8) 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 not 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 Meaning in these 10 numbers, the 8th smallest is the 3rd largest. INTERESTING Note: If two values are the same, they have the same rank. If we wanted to assign unique ranks to all values, we can do so by computing the rank of a value, and then adding the number of equal values before it. Let's call this the "unique rank". -->See the RSLO worksheet A33:F51. -->Note that Alice (A35) and Ilene (A31) both have a score of 31 -->Alice and Irene both are rank 11 (column C); no one has Rank 12 (14 is the --> next rank) -->Alice's augmented rank is 11 and Irene's is 12 (column D); see the --> formulas in in D35:D51: --> =RANK(B35,$B$35:$B$51)+COUNTIF(INDIRECT("B35:B"&(ROW())),B35)-1 -->Ilene is one higher rank than Alice because Ilene is the 2nd person with --> that score -->Column E recovers the names (using reference functions and the Indexes in --> column F) in rank order, with Alice directly followed by Ilene -->Experiment with other equal scores The second and third functions 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/first largest value in range; SMALL(range,2)/LARGE(range,2) returns the second smallest/second 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: for example in I2 and J2 the formulas are =SMALL($F$2:$F$11, E2) and =LARGE($F$2:$F$11, E2) With the column E storing the Indexes 1, 2, etc. So row 2 (using Index 1) shows the 1st smallest and 1st largest values in columns I and J; row 3 (using Index 2) shows the 2nd smallest and 2nd largest values in columns I and J. So Column I shows the values sorted from smallest to largest (1st smallest, 2nd smallest, ...) and Column J shows the values sorted from largest to smallest (1st largest, 2nd largest, ...). 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 in the quarter. 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. For example, the first formula says first find the rank (1 is largest) of value in the range, second use the rank to select the rank largest number. So if the rank of a V in a range is 3, and we select the 3rd largest value in that range, it will be V. -------------------------------------------------------------------------------- 9) The OFFSET function: The OFFSET function generalizes INDEX: given a starting cell, INDEX can select either a cell or a range; 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 a reference-cell specifies the cell at row R and column C. The resulting range is from: row R + row offset and column C + column offset to: row R + row offset + height-1 and column C + column offset + width-1 So, the range starts at (R,C) shifted by row offset and column offset; 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 address, e.g. F16, and the others are --> all small integers); notice the range specified by the offset (its text --> appears in cell B21), 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 row offset and column --> offset s (normally these values are non-negative, but they are allowed to --> be negative, going backward from the reference cell). In fact, row offset --> and column offset are often 0 (specifying the reference cell), with 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 more clumsily 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 INDIRECTs, 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. We can use OFFSET to easily compute the sum of the Nth column or row in a table that is 5x5 (like the F16:J20). Cell B27 shows what column we want summed (1st, 2nd, etc) and cell B28 is the formula =SUM(OFFSET($E$16,0,B27,5,1)) Column E is 1 to the left of the first column in the table (so adding 1 would give us the first column) and 16 is the top row of the table. We add 0 to the rows (still row 16) and B27 to the columns to calculate the row start and column start, then select a height of 5 rows and 1 column. B30 and B31 does the same thing with computing the SUM of a selected row: =SUM(OFFSET($F$15,B30,0,1,5)) Column F is left column of the table table and 15 is one less than the first row (so adding 1 would give us the first row). We add B30 to the rows and 0 to the columns (still column F) to calculate the row start and column start, then select a height of 1 row and a width of 5 columns. -------------------------------------------------------------------------------- 10) 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 with them in Excel. Know the meaning of and how to use the following functions (here listed both in the order they appear in the notes and alphabetically) showing their arguments and returned result. 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. Below, range/cell means either a range or cell. A linear range* is a range that is purely a row or purely a column, not a rectangle; nothing* (for COLUMN or ROW) shows the integer column or row in the cell IN WHICH THE FUNCTION APPEARS. This is used/useful for conditionally formatting cells with a selected row/column (see many examples on the IM+L and RSLO worksheets accompanying this lecture). Notes order parameter(s) to function result of function ------------------------------------------------------------- INDIRECT text describing range/cell range/cell INDIRECT("A1:A15") A1:A15 LEN text integer FIND text,text,[integer] integer LEFT text,integer text RIGHT text,integer text MID text,integer,integer text (see notes for examples) ADDRESS 3 integer: row,col,[mode] text describing cell (e.g., "$A15") ADDRESS(3,5,1) "$E$3" mode=1 is absolute) COLUMN cell/nothing* integer column of cell COLUMN(E3) 5 ROW cell/nothing* integer row of cell ROW(E3) 3 CHOOSE integer, values/ranges value/cell/range CHOOSE(2, "A", "B", "C") "B" ROWS range/cell integer count: number of cells in row ROWS(A1:A15) 15 (rows 1-15) COLUMNS range/cell integer count: number of cells in column COLUMNS(A1:E110) 5 (columns A, B, C, D, and E) INDEX range,1-2 integers cell/linear range* MATCH value,linear range*,integer integer index of value in linear range* LOOKUP value,2 linear ranges* value at index in linear range* VLOOKUP value,table,offset column value in offset column in matched row RANK value,range,[integer] integer ranking of value in range SMALL linear range*,integer value in range LARGE linear range*,integer value in range OFFSET reference-cell,4 integers range/cell Alphabetical parameter(s) to function result of function ------------------------------------------------------------- ADDRESS 3 integers: row,col,[mode] text describing cell COLUMN cell or nothing* integer column of cell COLUMNS range/cell integer count: number of cells in column FIND text,text,[integer] integer INDEX range,1-2 integers cell/linear range* INDIRECT text describing range/cell range/cell LOOKUP value,2 linear ranges* value at index in linear range* LARGE linear range*,integer value in range LEFT text,integer text LEN text integer MATCH value,linear range*,integer integer index of value in linear range* MID text,integer,integer text OFFSET reference-cell,4 integers range/cell RANK value,range,[integer] integer ranking of value in range RIGHT text,integer text ROW cell or nothing* integer row of cell ROWS range/cell integer count: number of cells in row SMALL linear range*,integer value in range VLOOKUP value,table,offset column value in offset column in matched row Recognize the power of building text that specifies a cell/range, and converting that text into a cell/range with INDIRECT. Understand how to lookup information via the INDEX/MATCH functions, and when possible and simpler, the LOOKUP function. OFFSET sometimes can take the place of more complicated combinations of these functions. Bottom Line: The purpose of reading these notes is not just to follow the instructions and see the results; it is to understand what you did and the connection between what you can do and what the results will be, in general. So, in new situations where you need to apply the skill, you'll know what to do because you actually learned the skill while following the instructions in these lectures and thinking about what you did.