Conditional Functions (If, Table Lookup)



1.9 - Reference Functions In a previous chapter it was shown how IF functions can be nested to decide between a set of possible outcomes. IF functions, however, are limited to seven levels of nesting, allowing for at most eight different outcomes. Furthermore, it quickly becomes apparent after two or three levels of nesting this method can be cumbersome and time consuming to implement. Excel provides another category of functions designed to look up values from a given list and return a corresponding value. In this section, we will use one of the simplest Reference functions, the LOOKUP function.The Lookup FunctionThe Lookup Function SyntaxThe LOOKUP function is a function designed to look up values from a given list and return a corresponding value. The LOOKUP function takes a specified criterion (either a label or a value) and searches for a matching entry in a one-column or one-row range. When a match is found, it returns the cell contents (either a value or label) from the same position of a corresponding one-column or one-row range. The LOOKUP function has two syntax forms: vector and array. This chapter will discuss with the vector form of the LOOKUP function:LOOKUP(lookup_value,lookup_vector,result_vector)The arguments of the vector form of the LOOKUP Function syntax are as follows:Lookup_value is a value that the LOOKUP function searches for in the first vector. This value can be a number, text, logical value, or a name or reference that refers to a value. It can be thought of as the criteria you wish to match.Lookup_vector is a range of cells used to search for the lookup_value. This range must be one dimensional, containing either cells in a single row or a single column. The values in lookup_vector must be placed in ascending order.Result_vector is a range of cells that contain the values you wish to return. It too contains either one row or one column, and must also be the same size as lookup_vector.The results of a LOOKUP function can be one of the following:If the value you are looking for exactly matches a value in the lookup_vector then it will return the corresponding entry on the result_vector. If the lookup_value does not match an entry in the lookup_vector exactly, it is matched to the largest value in lookup_vector that is less than or equal to the lookup_value. If the lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value. To illustrate how the LOOKUP function works, consider the following formula based on the worksheet in Figure 1:=LOOKUP(“Jane”, A2:A5, B2:B5).Figure 1This formula will match Jane to the entries in the column range A2:A5. It will return the corresponding value in the column range C2:C5. If Jane does not appear it will find the “closest” match alphabetically. In this case the formula finds Jane in the second row of the lookup_vector range, so it will return the second value in the result_vector range, which corresponds to the value 17.The Lookup Function Using a horizontal orientation13620751607185Here is an example of a lookup table in a horizontal format. In Figure 2 cells A1:E2 contain a tax schedule. Taxes are calculated on a progressive scale: incomes under $25,000 have no tax (0%), incomes of at least $25,000 but less than $50,000 are taxed at 10% of income, incomes of at least $50,000 but less than $100,000 are taxed at 15% of income, and incomes of $100,00 or more are taxed at the rate of 23%. In cell A5:C10 is a list of employees and their taxable income. In cell C6, write a formula to determine the income tax for the employee Jonas, such that it can be copied down the column. Figure 2For just Jonas the solution =B6*D3 can be used – Jonas’s income multiplied by the corresponding tax rate. However this approach will not work when the formula is copied down the column: income varies relatively (B6) but the tax rate (D3) neither stays the same nor changes relatively, as it is dependent on the income value. Another way to approach this would be to use a nested IF function: = B6*IF(B6<C2, B3, IF(B6<D2,C3,IF(B6<E2, D3, E3)))While this function works, it’s somewhat tedious to write and if more tax levels are added, the formula is difficult to maintain. Furthermore, if there are more than 8 different tax levels, this approach will no longer work. A more efficient method of solving this problem is to use a LOOKUP function:=B6*LOOKUP(B6,B$2:E$2,B$3:E$3)This formula will multiply the value $73,250, the income in cell B6, by corresponding tax rate. To find the corresponding tax rate the LOOKUP function will match the value in cell B6, the first argument of the function, to the values in the range B2:E2, as specified in the second argument of the function. Once a match is found, the corresponding value in the result vector (the third argument specified as B3:E3) will be returned.Note several features of this formula:The lookup value is a cell reference. This reference will copy relatively to match each successive employee’s income.The lookup and result vectors contain absolute row references so that when the formula is copied down these ranges will not be affected.The values found are NOT exact matches. i.e., $73,250 does not appear in the lookup vector range B2:E2. Rather the greatest value in the lookup vector range that does not exceed the lookup value is selected and the corresponding value in the result vector is returned. Both the lookup_vector and result_vector ranges do not include titles. These ranges start with the first value and end with the last value – no additional cells should be included in the range.How is the corresponding value found? The exact algorithm (step-by-step method) is not specified but one possible logical method might be as follows:The first category $0 is less than the lookup value $73,250 so the next value is considered.The second category $25,000 is again less than the lookup value $73,250 so again the next value is considered.The third category $50,000 is less than the lookup value $73,250 so the next value is considered.The fourth category $100,000 is more than the lookup value $73,250 so the previous value is matched – that of $50,000. $50,000 is the third value of the lookup vector, so the third value of the result vector, 15% in cell D3, is returned.Again this is not the “exact” method necessarily programmed into Excel. What is known is that the user must list the lookup values in ascending order or the function is not guaranteed to return the correct result.A more complex example:Orders!Figure 3294322534290095250123825Pricing!A more complex example is given in Figure 3. This workbook example contains two worksheets: Sheet Pricing! contains a list of items numbers for various items you have for sale on E-Bay, their corresponding cost per unit, and weight in pounds (lbs) per unit. Also given on this worksheet is the shipping cost per pound. Worksheet Orders! contains a list of recent orders, including the item number being ordered and quantity. To complete the Orders! worksheet column D requires a formula to total the cost of this customer’s order, including the cost of all items and shipping.Step 1 - Understand the Problem.Mathematically the customer’s order total is Quantity * (Price/Item + Shipping Cost/Item). Since just the shipping weight in pounds (lbs) and cost per pound ($/lb) are given, this formula can be further expanded to the following:Quantity * (Price/Item + Weight/Item * $/lb to ship)Step 2 – Translate to Excel Syntax. If this formula is written directly, just substituting the appropriate values for Zael’s order, then the cost per unit and weight would not necessary copy down correctly. Both of these values are variable, but change by item number and not by relative position. Thus a LOOKUP will be required. Can a single LOOKUP function return both the cost per item and shipping weight? No, the LOOKUP function can return only one value at a time. So each variable, price/item, and weight/item, will require a separate LOOKUP. In pseudo code this formula will be: Quantity * (Lookup(item number, range of item numbers, range of prices) + Lookup(item number, range of item numbers, range of weights) * $/lb to ship). The cell references are as follows:The quantity is on the same worksheet as the formula (Orders!) in cell C3.The item number to be looked up is on the same worksheet as the formula in cell B3.The range containing the item numbers to lookup is on sheet Pricing! in cells A5 through A17.The range containing the corresponding prices is on sheet Pricing! in cells B5 through B17.The range containing the corresponding weight is on sheet Pricing! in cells B5 through B17.The cost per pound to ship is on sheet Pricing in cell C2.Applying these cell references the formula is: =C3*(LOOKUP(B3,Pricing!A5:A17,Pricing!B5:B17)+LOOKUP(B3,Pricing!A5:A17, Pricing!C5:C17)*Pricing!C2)Step 3 – Copying. In addition to knowing that a LOOKUP function is needed so that the formula can be copied, some of the arguments of the LOOKUP and the other operands must be considered. The Quantity and Item Number will change relatively as the formula is copied down the column so thus C3 and B3 are relative references. However, the lookup_vector and result vector ranges do not change when copied and therefore must contain an absolute row reference. Lastly, the $/lb for shipping in cell Pricing!C2 needs to be considered. Again this value does not change when the formula is copied and thus requires an absolute row reference. The final formula will be:=C3*(LOOKUP(B3,Pricing!A$5:A$17,Pricing!B$5:B$17)+LOOKUP(B3,Pricing!A$5:A$17,Pricing!C$5:C$17)*Pricing!C$2)One point worth noting is the value that results in cell Orders!D5 is #N/A. This is an error message indicating that there is no corresponding answer. The reason for this is the lookup_value 104 is below the smallest value on the lookup_vector list (124). This error can be corrected either by changing the item number on the order to an existing item number from the list, or by adding item 104 to the top of the price list. A more serious error would be using an item number that is between the values in the lookup range or greater than those values. For example if the value 138 is used as an item number, Excel will return the greatest value on the list that does not exceed 138. Hence the pricing information for item 136 will be returned and not an error message. This is one of the drawbacks of the simple LOOKUP function. Other reference functions can be used to find only an exact match, but those will not be covered in this course. You may read about these functions using the Help feature in Excel. Exercise 1.9-1 Reference functions: Pricing Copies Pricing!0122555Delivery!15240002369820Write a formula in cell B13 that can be copied down the column to determine the total cost of making copies. The number of copies to be made is listed in Column. A.The company also offers delivery. Their delivery charges are as follows: For orders under $5, there is a $5 delivery fee. Orders at least $5 but smaller than $20 there is a $7 delivery fee. Orders of $20 or more are delivered free of charge. Set up a reference table on the Delivery! worksheet so that you can write a reference function to calculate the delivery cost for each order. Organize the table in a horizontal format.Write a formula that can be copied down to calculate Delivery Cost in cell C13. Use the reference table you created in question 2.Exercise 1.9-2 reference functions: GradebookSheetname: Grades1!Sheetname: Grades2!3581400-10350545720139704857753810Sheetname: scores!Use cell references whenever possible in your formulas:1. Write a formula in cell scores!G2 that can be copied down the column to determine the percent of total possible points Mickey Mouse earned.2. Write a formula in cell scores!H2 that can be copied down the column to determine Mr. Mouse’s final letter grade. Use a reference function and the grading scale on sheet Grades1!.3. Write another formula in cell scores!H2 that can be copied down the column to determine Mr. Mouse’s final letter grade. Use a reference function and this time use the grade listing on sheet Grades2!.4. How could you solve this problem without using a reference function?-123825333375277177539052501863090Disc!33051751940560Exercise 1.9-3 reference functions: Sales Order Pricingdiscount!sales!ship2!ship1!Above are some worksheets you have prepared to keep track of customer sales. The worksheet sales! lists each sale made by your company. It will be your job to calculate the shipping costs and factor in the appropriate discounted sales price for each sale. Shipping charges from $1 to $99.99 are 20% of the total sale, from $100 to $999.99 shipping charges are 10% of the total sale, and over $1000 are 5% of the total sale. Each company has negotiated different discount rates for discount types A and B as given on sheet discount!. Discount A is applied to sales under $200. Discount B is applied to sales of $200 and above. Discounts are calculated based total cost w/o discount (sales plus shipping) that you will calculate in column D.1. If you were to use a reference function in cell sales!C2 to calculate the shipping cost of the $345 sale (cell sales!B2), would you want to use the data on sheet ship1! as the reference table or the data on sheet ship2! as the reference table? Why?2. Using a LOOKUP function, write a formula in cell sales!C2 that can be copied down the column to calculate the shipping cost of the corresponding sale in that row. 3. What other method can be used to solve question #2? Is this method better or worse? Why?4. Write a formula in cell sales!D2 that can be copied down the column to calculate the total cost before discounts (list price plus shipping).5. Write a formula in cell sales!E2 that can be copied down the column to determine the discounted price of the order. Remember that discounts are based on a company’s negotiated discount rate and the total cost of the order (discount A for <$200 or discount B for >=$200).6. Your competitor charges a $25 flat shipping rate per order. Write a formula (True/False) in cell sales!F2 to determine if your customer paid more than $25 for his/her shipping.Exercise 1.9-4 Lookup Functions: – Chapter Review – The three spreadsheets on the following page are all worksheets in a single workbook. The worksheet named Prod! lists product information (shipping time, cost, and shipping cost) by product number. The worksheet named Ship! is a table of shipping costs for the corresponding product costs. The worksheet named Order! is a list of customer orders (one item type per order) and the order quantity. Write a formula in cell Prod!D3 using a reference function to calculate the shipping cost for product 0001. Write the formula so that it can be copied down the column. Write a formula in cell Order!D3 to calculate the total cost of this order (product costs and shipping costs). Write the formula so that it can be copied down the column. Write a formula in cell order!G3 to determine (true/false) if the shipment was late. A shipment is late if it took more that the required number of shipping days to be received. Write the formula so that it can be copied down the column. Is it possible to write a LOOKUP function in cell Order!G20 to determine the customer name of the customer with the lowest value order? Why or why not?Write a formula in cell Prod!D3 using a nested if function to calculate the shipping cost for product 0001. Write the formula so that it can be copied down the column. 4762570485-323850146685Sheetname: Order!Order!Ship!Prod!-7048502113280Sheetname: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download