Excel: 12 Using Powerful Functions: Logical, Lookup, and Database Functions

Examples of Database Functions

A. Using DSUM to Conditionally Sum Records from a Database =DSUM (database, field, criteria) will add records from one field in a data set, provided that the records meet some criteria that you specify database-the range of cells that make up the list of database field-this indicates which column is used in the function. In specifying a field, you can point to the cell with the field name such as H23 for Revenue, you can include the word Revenue as the field argument or you can use the number 8 to indicate that Revenue is the eighth field in the database criteria-this is the range of cells that contains the conditions specified. This typically includes at least one column label and at least one cell below the column label for specifying a condition for the column. B. Creating a Simple Criteria Range for Database Functions The headings along with at least one additional row will create a criteria range. C. Using a Blank Criteria Range to Return All Records If the second row of the criteria is blank, the result reflects all rows. This is equivalent to using the SUM function. D. Using AND to Join Criteria When two criterion are on the same line, they are joined by an AND function; rows must meet both criteria to be included in the DSUM. E. Using OR to Join Criteria When two criteria are placed on different rows, they are joined by an OR function; rows can meet either criteria to be included in the DSUM. The criteria to be joined with OR can be in separate columns. F. Using Dates or Numbers as Criteria You can use any of these formats: >12/31/2014 >=1/1/2015 >31 - Dec - 2014 G. Using the Miracle Version of a Criteria Range The criteria range consists of a range that is two cells tall and one or more cells wide. The top cell of the criteria range cannot contain a field heading with the exception of "Computed Criteria" The second row in the criteria range can contain any formula that evaluates to TRUE or FALSE. This formula must point to cells in the first data row of the database. This formula can be as complex as you wish with AND, OR, VLOOKUP, NOT, and MATCH; it can contain any combination of functions. =NOT(ISNA(MATCH(A24, $K$38,0))). H. Using the DGET Function =DGET(database, field, criteria) returns a single cell matching criteria from a data set. Write a criteria record that causes one and only one row to be evaluated as TRUE.

Examples of Logical Functions

A. Using the IF Function to Make a Decision =IF(logical_test, value_if_true, value_if_false) To test if the arguments are whether TRUE or FALSE. =IF(B5="West", A2>100, C99<=D99) Comparison Operators Table 12.6 =IF(I2=1.5, H2>=50%, 2.5) The commission rate is 1.5 percent of revenue then gross profit percentage is 50% or higher otherwise the commission rate is 2.5 percent of revenue =IF(H2>=50%, 0.025*F2, 0.015*F2) =IF(ISREF(Hi), Hi*2, "Name Range Has Been Deleted") The spreadsheet is called Hi If, then, otherwise If the reference is hi, then it is hi *2, otherwise name it name has been deleted B. Using the AND Function to Check for Two or More Conditions =AND(logical1,logical2,...) All of the arguments must be TRUE to meet every condition. to determine if all conditions in a test are TRUE A retail store manager offers a $25 bonus for every leather jacket sold on Friday this month. If the value is a jacket and whether the date falls on a Friday. =AND (E2="Jacket", WEEKDAY(D2,2)=5) is FALSE C. Using the AND Function to Compare Two Lists To compare an original list (A-E) to copied list (I-N) =AND (A6=I6, B6=J6, C6=K6, D6=L6, E6=M6) To compare the data set in one data set. G2 is =AND(G6:G999) To test if all of the arguments is TRUE in the column, use the Find What box to search for a single FALSE to revise. The arguments may not be true in case of an honest mistake when dropping down the formula in the column. In that you can revise your columns to absolute reference and row to relative reference. Other similar queries: 1. =MULTIPLICATION(A6=I6)*(B6=J6)*(C6=K6)*(D6*L6)*(E6=M6), Find and Replace dialog Find What Box: FALSE Look In drop-down: Formulas to Values 1 is TRUE and 0 is FALSE 2. =AND(A6:E6=I6:M6) D. Using OR to Check Whether Any Conditions Are Met =OR(logical1,logical2,...) Returns if any of the arguments are TRUE and returns FALSE if none of the conditions are TRUE. The revenue is over 50,000 the customer is new this year is D2 = 2010. =OR(D2=2010, E2>50000). You can use the OR function as the first argument in the IF function to produce the formula =IF(OR(D2=2010, E2>50000), 0.025E2, 0.015E2) E. Nesting IF Functions The logical test is TRUE and the first formula is used The logical test is FALSE and the second formula is used. Employees are ranked on a 5 point scale: 4.5 or higher: 5 percent raise 4 or higher: 4.5 percent raise 3.25 or higher: 3 percent raise 2.5 or higher: 1 percent raise Under 2.5: no raise TRUE: =IF(B2>=4.5, 5%, IF(B2>=4, 4.5%, IF(B2>=3.25, 3%IF (B2>=2.5, 1%, 0%)))) This formula contains four nested IF functions and sets the raise for employees. F. Using the TRUE and FALSE Functions =IF(OR(A2>5,B2=0), TRUE(), FALSE()) You can replace the function with values with TRUE or FALSE. =IF(OR(A2>5,B2=0), TRUE, FALSE) G. Using the NOT Function to Simplify the Use of AND and OR Old functions in Excel: NAND=Not And NOR=Not Or XOR=Exclusive =NOT (logical) NOT reverses a logical value. TRUE becomes FALSE, and FALSE becomes TRUE when processed through a NOT function. To find Tulsa and Oklahoma City =(OR(A2="Tulsa", A2="Oklahoma City")) or NOT =NOT(OR(A2="Tulsa", A2="Oklahoma City")) H. Using the IFERROR Function to Simplify Error Checking =IFERROR (Something is True, then do something, otherwise do something else) #DIV/0! - The Division-by-zero error is caused by the a zero in the divisor. To check if the divisor is a true 0, then perform the IF function: =IF(C5=0, 0, B5/C5) If the divisor is a zero, the formula returns a 0, otherwise perform the calculation. To avoid #N/A errors whenever you have an empty value from data entry not being inputted: You can write this error: =IF(ISNA(VLOOKUP(B7, $F$2:$G$9;2,FALSE)), "New Rep", VLOOKUP(B7, $F$2:$G$9, 2, FALSE)) Retrieves a value from a lookup table. =IF(first find the rep name in the lookup table, if the rep is not found and returns the error, then use some other test, which in this case are the words New Rep. If the rep is found, then perform the lookup again and use that result. =IFERROR(value, value_if_error) The calculation is only evaluated once. If the calculation results in any of the errors listed in the following: #N/A = 1 #VALUE! = 2 #REF! = 3 #DIV/0! = 4 #NUM! = 5 #NAME? = 6 #NULL! = 7 Then Excel returns an alternative value. If the calculation results in any other valid value (numeric, logical, or text), Excel returns the calculated value. From: =IF(ISNA(VLOOKUP(B7, $F$2:$G$9;2,FALSE)), "New Rep", VLOOKUP(B7, $F$2:$G$9, 2, FALSE)) TO: =IFERROR(VLOOKUP(B7, $F$2:$G$9,2,FALSE), "New Rep")

Examples of Information Functions

A. Using the IS Functions to Test for Errors =ISERROR To test whether a calculation or value results in any type of error. If you have a legacy of Excel (2007 version and earlier), use this function with the IF function rather than the IFERROR function =IF(ISERROR (A2), "Unknown", A2) =ISERR This is similar to ISERROR except it does not report #N/A errors. =ISNA This function specifically tests whether a result returns an #N/A error. =ERROR.TYPE - This function lets you know specifically what error is being returned. This function returns a value from 1 through 7 to indicate #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, and #N/A error types. You will be writing a long function, but it will give you a friendlier error message. =IF(NOT(ISERROR(A2)), A2, CHOOSE(ERROR.TYPE(A2), "Null Value Found", "Division by Zero", "Invalid Value", "Missing Reference", "Undefined Name", "Numeric Error", "Value Not Available") B. Using IS Functions to Test for Types of Values =ISBLANK-Returns TRUE only if a cell is completely empty. A cell is not considered empty if the cell has several spaces and contains apostrophes =ISEVEN -This function indicates if a number is evenly divisible by 2. Numerical values. If the cell contains the numeric value 0, then it returns even. Using text, date or logical values in the ISEVEN functions causes #VALUE! error. FALSE if it is not even and TRUE if it is even =ISODD-This function indicates whether a number is not evenly divisible by 2. An empty cell is considered zero and returns FALSE to ISODD. The same limitations applied for ISEVEN is to ISODD functions. FALSE if it is not odd and TRUE if it is odd *If the value contains decimal places, regardless if the decimal place can be rounded up to a whole integer, they are ignored by both the ISEVEN and ISODD functions. Numbers and decimal places like 1.02, 1.2, 1.5, 1.99999 are TRUE for ISODD function. =ISLOGICAL- Test if the value in text is either TRUE by having it expressed as either TRUE or FALSE. If the text has quotation marks, then it is still FALSE. =ISTEXT-Returns TRUE if the value contains text. This is good for finding values such as ABC in Cell A16 and for finding cells that look like numbers but are actually stored as text. All kinds of phrases entered is TRUE, except if the value has quotation marks. =ISNONTEXT-This returns TRUE for anything that is context. Numbers, logicals, dates, empty cells, and even error cells return TRUE. =ISNUMBER-Returns TRUE for numeric cells and dates. =IF(ISNONTEXT(C5), RIGHT("0000"&C5,5), C5) fixes the errant zip codes (Excel acknowledges the extra "0" in the zip code of 04123). If the value in Column C is nontext, the program pads the left side of the zip code with zeros and then takes the five right most digits. C. Using the ISREF Function =ISREF(Value) Returns if the value is a valid reference TRUE: =ISREF(A2) a cell reference ISREF(XFD1048576) a serial number ISREF(A2:Z99) a range FALSE: =ISREF("A2") with quotation makrs ISREF(99) a number ISREF(2+2) a math operator =IF(ISREF(ExpenseTotal), ExpenseTotal*2, "Name Range Has Been Deleted') to check if the sheet name called Expense Total has been deleted. You can use the ISREF function to reference. a. Using the ISREF Function to Check a Reference =ISREF(INDIRECT(D14)) The INDIRECT function returns the value stored in a cell referenced as either TRUE or FALSE. Ex. Select a row: 2 Select a number: 4 Cell Address: D2 [=CHAR(64+D13)&D12] Indirect: 370 [=INDIRECT(D14)] IsRef: TRUE [=ISREF(InDirect(D14))] D. Using the N Function to Add a Comment to a Formula =N converts a value to the following: N(any number) = returned that number N(a date) returned the serial number of the date N(True) returned to 1. N(False) returned to 0. N(any error) returned the error N(any text) returned to 0. E. Using the NA Function to Force Charts to Not Plot Missing Data To keep track of the total collected throughout the fund drive. You may want to have #N/A! errors to produce the correct look to the chart =IF(ISBLANK(A15), NA() , A15+C14) compared to =IF(ISBLANK(A15)," ", A15+C14). F. Using the INFO Function to Print Information About a Computer =INFO (type_text) returns information about the current operating environment The following are valid values for this argument: Directory-returns the folder where the current workbook is saved. NumFile-Returns the number of open files, including workbooks and ALL files in the system. MemAvali-Returns the available memory. MemUsed-Specifies the memory in use by Excel TotMem-Returns the total of the previous two results Origin- returns the value shown in the upper left corner of the visible window. =INDIRECT(TRIM(MID(INFO("Origin"), 2, 50) OSVersion-Returns the version number of your operating system. Recalc-Returns either Manual or Automatic to indicate the current recalculation status. You might provide a hint to the spreadsheet reader with =IF(INFO("Recalc")="Manual", "Press F9 to calculate", " ") Release-Specifies the release number of Excel. For Excel 2007, this is 12.0. You might be able to use this information in combination with IF and INDIRECT to correctly build a reference to the entire worksheet. System- Returns either mac or pcdocs to indicate Macintosh or Windows. G. Using the CELL Function Can tell you specific information about a specific cell or specific information about the last cell changed in the worksheet. =CELL (info_type, reference) the functions works better if you use double quotations on the info_type. specify the type of information and optionally a cell reference. If you leave off the cell that is referenced then it returns information about the last cell changed in the workbook. Contents-Returns the value in the upper-left cell in reference Address-Returns the address of the first cell in reference (absolute reference), as text. Row-Returns the row number of the cell in reference (the referenced cell is test located in cell A1). Col-Returns the column number of the cell in reference Filename-Returns the filename as text including the full path of the file that contains reference. Format-Returns the text value corresponding to the number format of the cell. Parentheses-Returns 1 if the cell is formatted with parentheses for positive or all values; otherwise, returns 0. Color-Returns 1 if the cell is formatted in color for negative values, otherwise returns 0. Prefix-Returns the text value corresponding to the label prefix of the cell as follows: (') if the cell contains left-aligned text (") if the cell contains right-aligned text (^) if the cell contains centered text (\) if the cell contains fill-aligned text (" ") if the cell contains anything else Protect- Returns 0 if the cell is not locked and 1 if the cell is locked. Remember that by default, all Excel cells start with their locked property set to TRUE. The locked property is taken into account only if protection is enabled. This argument for the CELL function reports a 1 even if protection is not turned on. Type-Returns the text value corresponding to the type of data in the cell as follows: Returns b for blank if the cell is empty Returns 1 for label if the cell contains a text constant Returns v for value if the cell contains anything else Width-Returns the column width of the cell, rounded to an integer. Each unit of column with is equal to the width of one character in the default font size. Reference- Is an optional cell reference. If reference is omitted, CELL returns the information about the last changed cell. Reference Data May!A2+June!A2 May! is the worksheet named A2 is the cell you want to look up in May June! is the second worksheet you want A2 is the cell you want to look up in June Referencing a range of cells.... [workbook name..xls]worksheet name!C1:C34 in other worksheets: worksheet!A1:D4 in other workbook: c:\my documents[test.xls]Sheet1!A2:A5 Across several worksheets: sheet1:sheet5! A12 H. Using CELL to Track the Last Cell Changed If you leave off the second argument of the CELL function, Excel returns the information about the last cell changed in the workbook. Create a watch window of the last cells changed: 1. In an out of the way spot, enter the formula =CELL("address"). 2. Just below this formula, enter the formula =CELL("contents") 3. Just below that formula, enter the formula =CELL ("filename") 4. Select all three of these cells 5. From the Formulas tab, select the Watch Window icon, then the Watch Window dialog appears. 6. Click the Add Watch button in the Watch Window dialog 7. View the complete Value and Formula columns Then, you will have a floating window. Note, the last changed cell might be on another worksheet. I. Using TYPE to Determine Type of Cell Value =TYPE(value) to determine whether a value is a number, text, logical, an error value, or an array. Note that dates are treated as numbers. Returns a numeric code that tells you what type of value: 1-For a numeric or date type 2-For a text type 4-For a logical type 16-For an error type 64-An array type

Powerful Functions

Alphabetical List of Logical Functions, Information Functions, Lookup Functions, Database Functions, External Functions

Examples of Lookup and Reference Functions

The lookup and reference icon contains 18 functions. The VLOOKUP function allows you to perform the equivalent of a join operation in a database. Combined functions allows for very powerful manipulations of data A. Using the CHOOSE Function for Simple Lookups =CHOOSE (index_num, value1, value2,...) The CHOOSE value chooses a value from a list of values, based on an index number. The CHOOSE function takes the following arguments: Index_num-This specifies which value argument is selected. Index_num must be a number between 1 and 254 or a formula or reference to a cell containing a number between 1 and 254: If index_num is 1, CHOOSE returns value 1 If index_num is a decimal, it is rounded down to the next lowest integer before being used. If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns a #VALUE! error Value1, value2,... These are 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text. =CHOOSE (G4, "Strongly Disagree", "Disagree", "Neutral", "Agree", "Strongly Agree"). This function ranks the respondent's average score corresponding on the category.n B. Using VLOOKUP with TRUE to Find a Value Based on a Range VLOOKUP stands for vertical lookup. Describes where you need to choose a value based on a table that contains ranges. Ex. 92-100 is an A 85-91 is a B 70-85 is a C 65-69 is a D Below 65 is an F =VLOOKUP(B2, $E$2:$F$6, 2, TRUE). The cell is the students score, range of score criteria (0, F; 65, D; 70,C; 85,B; 92,A), the column number of the score, you are using the sorted range variety of lookup. C. Using VLOOKUP with FALSE to Find an Exact Value =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) =VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match - indicated as 0/FALSE or 1/TRUE). =VLOOKUP(value to lookup, cell range to search, column from which to take corresponding date) VLOOKUP searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table. Lookup_value-This is the value to be found in the first column of the table. Lookup_value can be a value, reference, or text string. Table_array-This is the table of information in which data is looked up. You can use a reference to a range such as E2:F9 or a range name such as a RepTable. Col_index_num-This is the column number in table_array from which the matching value must be returned. A col_index_num value of 1 returns the value in the first column in table_array and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of column in table_array, VLOOKUP returns the #REF! error value. Range_lookup-This is a logical value that specifies whether VLOOKUP should find an exact match or an approximate match. If it is TRUE or omitted, an approximate match is returned In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If it is FALSE, VLOOKUP finds an exact match. If one is not found, the error value #N/A is returned. If VLOOKUP cannot find lookup_value and if range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns an #N/A error. D. Using VLOOKUP to Match Two Lists =VLOOKUP(C3, $A$3:$A$15, 1, FALSE) is located in Column D is There? then double click the handle to form a list in order to determine if anyone is new. Column A is RSVPs Last Week and Column C is RSVPs This Week. If the record has a name, then it is because they person on RSVP from last week. In addition, the #N/A indicates, the individual is new. E. Using COLUMN to Assist with VLOOKUP When Filling a Wide Table =VLOOKUP($B6, $A$21:$M$176, C$4, FALSE) you can double click the handle . The C4 cell has the value of the column number so on C:N, however, you can eliminate the row above the table indicating the column number per column in the table with the COLUMN function shown below: =VLOOKUP($B6, $A$21:$M$176, COLUMN (B1), FALSE) =COLUMN (reference) The COLUMN function returns the column number a given reference. =COLUMN(A1) =1 F. Using HLOOKUP for Horizontal Lookup Tables Horizontal lookup, which is similar to VLOOKUP. If the fourth parameter value is FALSE, then HLOOKUP is looking for an exact match in the top row of the table (product codes, customer numbers, or any discrete bits of info.) If the fourth parameter value is TRUE, then HLOOKUP is treating the first row of the table as a sorted range of values. Excel looks for the closest lower value than the one you specified. This is fine when you are trying to determine in which range a value belongs. =HLOOKUP(lookup_value, table_array, row_index_num, range_lookup) searches a value in the top row of a table, when found, then it returns a value from a particular row in the column. Lookup_value-The value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string. Table_array-A table of information in which data is looked up. Use a reference to a range or a range name. The values in the first row of table_array can be text, numbers, or logical values. If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order: ...-2, -1, 0, 1, 2,... , A-Z; or FALSE, TRUE. Otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. Uppercase and lowercase text are equivalent. Sort the values in ascending order, left to right. For more information, see Sort data in a range or table. Row_index_num-The row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value. Range_lookup-A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. =HLOOKUP($B$2, $G$3:$L$8, 3, FALSE) The function is almost the same as VLOOKUP except the column is substituted for the row number. 3rd row starting from J4 as 1. G. Using the MATCH Function to Locate the Position of a Matching Value =MATCH(lookup_value, lookup_array, [match_type]) returns the relative position of an item in a range that matches a specified value in a specified order. Find the position of an item in a range instead of the item itself. lookup_value- The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. lookup_array- The range of cells being searched. match_type-The number -1, 0, or 1. The match_type argument specifies how Excel matches, 1, the largest value that is less than or equal to look_up value. then the lookup_array must be arranged in ascending order: -5 to 5, A-Z, or FALSE to TRUE. 0, matches exactly the value as the lookup_value in any order in the lookup_array, -1 the smallest value that is greater than or equal to the lookup_value. lookup_array must be placed in descending order, such as TRUE, FALSE; Z-A, or 2 to -2. If it is omitted, then it is assumed as 1. lookup_value with values in lookup_array. The default value for this argument is 1. 1. MATCH allows for wildcard matches (*) Asterisk matches any sequence of characters (?) matches any single character match_type must be 0 lookup_value is text 2. MATCH allows for a search based on an exact match, based on the number just below the value or a value greater than the or equal to the lookup value. =MATCH(39,B2:B5,1) that is at 39 or greater =MATCH(41,B2:B5,0) the exact value =MATCH(40,B2:B5,-1) that is less than the amount of 40 3. Does not distinguish between uppercase and lowercase letters when matching text values a. Using MATCH to Compare Two Lists =MATCH(C3, $A$3:$A$11, 0) Produces a faster result rather than VLOOKUP. The MATCH function is comparing values from the Column A Entries Last Week compared to Column C Entries this Week in the Column D There? The output is 2 because the text value in C3 (Columbia) is the same value in the Column A in row 2. As a result you have 2 indicating the second row from the A3:A11 range. Any #N/A values means the value is new since the previous week. H. Using INDEX and MATCH for a Left Lookup Returns the cell from a particular row and column of a rectangular range. =INDEX (array, row_num, [column_num]) or (Range, Row, Column) =INDEX (B5:D9, C1, C2) C1 is a reference to row 3 C2 is a reference to column 2 The answer is 31 =MATCH (B1, H2:H89,0) Search through Column H Cust # to find the row with the customer number that matches that one in Cell B1. In this case, C593 is in Row 12, which the 11th row of the table. B1 has a value of C593. =INDEX (F2:F89, WhichRow, WhichColumn) Search through the customer names in Column F Name WhichRow has already been found with the Match function that is 12. WhichColumn Because the range has only one column, this is either 1 or omitted. As a result, you have: =INDEX(F2:F89, MATCH(B1, H2:H89, 0), 1) I. Using MATCH and INDEX to Fill a Wide Table =MATCH(B6, $A$21:$A$14060,0) B6 is in the Item Column and returns the C529 text string C6 is in the Match Column and returns the 8005 value Finds an exact match for C529. The answer in C6 is 8005 means that product C529 is on the 8,005th relative row of the lookup range. In Column C, you have the MATCH function In D6, you are in the WH01 column with the first column of data. Enter the formula: =INDEX ($B$21:$M$14060, $C6, COLUMN (A4)) =INDEX (array covers column B Item to WH07, relative row number within the range in column C Match, the column number in column A Qty). You can add a conditional format in the cell if the formula evaluates to TRUE: =D6>=$A6 =INDEX(A6:A15, MATCH (H5, B6:B15,0)) The length array has to be both the same in both of the columns in order to be sync. J. Performing Many Lookups with LOOKUP =LOOKUP(value to lookup, data to search, column with data you want) Can process many lookups in one single array formula, can deal with a lookup range that is vertical and a return range that is horizontal or vice-versa. Examines the height and width of the array: Rows>Columns VLOOKUP Rows<Columns HLOOKUP Because the array is wider than its tall HLOOKUP The LOOKUP function returns the value from the last column or row of the array =LOOKUP(lookup_value, lookup_vector, result_vector) You can use the VLOOKUP, HLOOKUP, or INDEX and MATCH. Specify the vectors either one row tall or one column wide =LOOKUP(A2,E2:G5) You can ask Excel to look up many values at one time, provided that you do the following: Control shift and enter Enclose the LOOKUP in a wrapper function such as SUM to summarize all the results from the function. {=SUM(LOOKUP(B4:B17,E6:F10))} A cell is calculated for the Bonus of GP% Gross Profit Percentage. The GP% corresponds to Bonus criteria (E6:F10) in a table with Invoice and GP% values (B4:B17). K. Using Functions to Describe the Shape of a Contiguous Reference =COLUMN (reference) Returns the numeric value of the column placed in the spreadsheet, =COLUMNS(reference)reference must be in a contiguous range. The total amount of columns in a range =ROW (reference) Returns the numeric value of the row placed in the spreadsheet =ROWS(reference) reference must be in a contiguous range. The total amount of rows in a range L. Using AREAS and INDEX to Describe a Range with More Than One Area =AREAS(reference) This function returns the number of contiguous ranges in a reference, refers to a named range =AREAS(MyRange) is 1 because there is only one range in the spreadsheet =INDEX(reference, row_num, column_num, area_num) returns reference of the intersection of row_num and column_num reference-reference to one or more cell ranges, enclose the reference in parenthesis if you are entering a nonadjacent range, If the reference contains only one row or column, then the other arguments are optional instead use =INDEX(reference, column_num) area_num-selects a range in reference from which to return the intersection of row_num and column_num. This selects a number of ranges in a table. (A1:B4, D1:E4, G1:H4), to select D1:E4 this will be 2. =CELL("width", INDEX (A1:B2, 1, 2) is =CELL("width", B1) The CELL function uses the return value of INDEX as a cell reference 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in Cell B1 =INDEX(MyAreas,,,1) to define the first area of the spreadsheet =INDEX(MyAreas,,,B$15) to define the first area in cell B15 =COLUMN(INDEX(MyAreas,,,B$15)) to define the value for column in the area M. Using Numbers with OFFSET to Describe a Range =OFFSET (reference, rows, cols, height, width) to describe a single cell or a rectangular range using mostly numbers . returns a reference to a range that is a given number of rows and columns from a given reference Can use the COUNT and lookup functions reference-cell reference or range of adjacent cells rows-number of rows from the upper left cell from the range. The row can be positive number going below or a negative number going above. Cols-positive going right from the starting reference and negative going left. ' Excel assumes the same height and width in Excel =SUM(OFFSET(A5,0,0,COUNT(A5:A999),1)) The height of the range and the width of the range is specified by ranges/cell =MATCH(E1, C1:C96,0) This is in cell I1 and has a value of 9 for row number for year =MATCH(E2,$A$8:$O$8,0) This is in cell I2 and has a value of 8 for column number for month =OFFSET(A1,I1-I, I2-1,5,1) In the range, there are a total of 5 rows including the title for May 2010 in one column. There are a total of 8 rows and 7 columns from the starting reference as default in A1. =COUNT(OFFSET(A1,I1-I2-1,5,1)) However, there are a total of 4 data entries for May 2010 as indicated in cell I3. =OFFSET(A1,I1-I2-1,I3,1) Use this function to figure out the min, max, and sum of May 2010 =MIN(OFFSET(A1,I1-I2-1,I3,1)) =MAX(OFFSET(A1,I1-I2-1,I3,1)) =SUM(OFFSET(A1,I1-I2-1,I3,1)) =AVERAGE (OFFSET (B3, COUNTA (B4:B15), 0, -6, 1)) The function will count in the Sales column (B3) in order to count the number of cells in the range of B4:B15 (the cells will have empty cells in order to enter data), you will be moving 0 to the right column, then you will be counting the last 6 months as the height above and 1 as the width, meaning in the same column. =SUM(OFFSET (A5, 0, 0, COUNT (A5:A999),1)). N. Using ADDRESS to Find Address for Any Cell =ADDRESS (row_num, column_num, abs_num, a1, sheet_text) returns the cell address an absolute address with both dollar signs. The ADDRESS function syntax has the following arguments: row_num-A numeric value that specifies the row number to use in the cell reference. column_num-A numeric value that specifies the column number to use in the cell reference. abs_num-A numeric value that specifies the type of reference to return. 1 or omitted Absolute 2 Absolute row; relative column 3 Relative row; absolute column 4 Relative A1 - A logical value that specifies the A1 or R1C1 reference style. In A1 style, columns are labeled alphabetically, and rows are labeled numerically. In R1C1 reference style, both columns and rows are labeled numerically. If the A1 argument is TRUE or omitted, the ADDRESS function returns an A1-style reference; if FALSE, the ADDRESS function returns an R1C1-style reference. sheet_text - A text value that specifies the name of the worksheet to be used as the external reference. For example, the formula =ADDRESS(1,1,,,"Sheet2") returns Sheet2!$A$1. If the sheet_text argument is omitted, no sheet name is used, and the address returned by the function refers to a cell on the current sheet. =ADDRESS (1,1,4,TRUE,"[C:\JanIncome.xls]Income Statement") The result is $AB$123 The cell address is in '[C:\JanIncome.xls]Income Statement'!A1 O. Using INDIRECT to Build and Evaluate Cell References On-the-Fly Looks for the specified cell and expects to a valid cell or range reference , then looks for the address to return The reference text can be any text that you can string together using various text functions. This allows you to create complex references that dynamically point to other sheets or to other open workbooks. The reference text can also be a range name. You could have a validation list box where someone selects a value from a lsit. If you have a predefined a name range that corresponds to each possible entry on the list, INDIRECT can point to the various named ranges on the fly. This also means no matter how someone rearranges the worksheet. =INDIRECT(ref_text, a1) returns the reference specified by a text string ref_text-contains A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If it refers to an external workbook, then it must be open, otherwise it will return #REF! a1-logical value that specifies what type of reference is contained. If a 1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. If a 1 is FALSE, then it's interpreted as an R1C1-style reference. Select all the worksheets (12) to Group Mode • In cell A1, enter the formula =A7. The cell contains the first date, which is the title for the worksheet. The cell A1 will be formatted to mmmm,yyyyy to appear January 2010. Ungroup the sheets by right clicking the Jan tab. • In cell D3, enter =D2 as the year to date formula • On the Feb worksheet, build a formula that returns the name of the previous month to appear as =Jan!D3 • In a cell, you can format January to appear as Jan. You can also use the =TEXT(A1, "mmm") function to achieve this result • To build the reference, =TEXT(DATE(YEAR(A1), MONTH(A1)-1,1), "MMM")&"!D3" • In cell D3, you will be inputting this formula: =TEXT(DATE(YEAR(A1), MONTH(A1)-1,1), "MMM")&"!D3")+D2 To achieve this, you must already selected the feb worksheet along with the other 11 worksheets in group mode. • The cell will automatically pull the year to date total from the previous worksheet and adds it to the current worksheet total! P. Using the HYPERLINK Function to Quickly Add Hyperlinks =HYPERLINK(link_location, friendly_name) creates a shortcut that opens a document stored on you harddrive, a network server, or the Internet link_location-this is the URL address on the Internet, it can also be a path, filename, and location in another file. Can be a text string enclosed in quotes or a cell that contains the link, ex. "[C:\files\Jan2007.xls]!Sheet1!A15" friendly_name-This is the underlined text or numeric value that is displayed in the cell. Can be a value, text string, a name, or a cell that contains the jump text or value. Q. Using the TRANSPOSE Function to Formulaically Turn Data =TRANSPOSE(array) Transposes a vertical range into a horizontal array, or vise versa. The argument is an array or a range of cells on a worksheet that you want to transpose. R. Using the RTD Function and COM Add-ins to Retrieve Real-Time Data =RTD(progrid, server, ropic1, [topic2],...) Returns real-time data from a program that supports COM automation. The COM add ins installed on your systems allows stream real time data to an Excel spreadsheet. Progid-this is the name of the Program ID of a registered COM automation add-in that has been installed on the local computer. You need to close the name in quotation marks. Server-this is name of the server where the add-in should be run. If there is no server and the program is run locally, leave this argument blank Topic1, topic2,...-These are 1 to 28 parameters that together represent a unique piece of real-time data S. Using GETPIVOTDATA to Retrieve One Cell from a Pivot Table =GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, item2,...) returns data stored in a pivot table report. To retrieve summary data from a pivot table report, provided that the summary data is visible in the report. When you click outside of your pivot table on a worksheet on a cell, the cell generates: =GETPIVOTDATA("Sales",$B$5, "Customer","Astonishing Glass Company","Region","West") To turn off this behavior: Select a cell inside an active pivot table. In the Pivot Table Tools tap, select the Option tab. From the PivotTable group, select the Options drop down and then select the Generate GetPivotData icon. Then enter the formulas without generating this function Data_field - the data you want to retrieve in quotation marks Pivot_table - this is a reference to any cell, range of cells, or named range of cells in a pivot table report. This is the information is used to determine which pivot table report contains the data (text string, number) you want to retrieve. Field1, Item1,...- these are 1-14 pairs of field names and item names that describe the data you want to retrieve. Item names must be in DATE or TIME