Electronic Spreadsheet (Excel) Handout 3



-72857467822A sum-mary of VLOOKUP on the last page00A sum-mary of VLOOKUP on the last pageElectronic Spreadsheet (Excel) Tutorial 3:VLOOKUP, Workbook, Conditional Formatting, and SortingDr. Yüe “Jeff” ZhangVersion 12.2, March 11, 2018?General Note: You are REQUIRED to read through the handout, and work through the exercises (if applicable), so you will come to class with a "prepared mind."Section 3-1: VLOOKUPWe have learned to use nested IF to write a formula to determine a student’s grade based on scores. We used four levels of IF to determine one among five grades (A, … F). However, what if we use “+/-” system, which has additional seven levels (A-, B+, …,)? We will then need to write a very complex nested IF formula which will exceed the allowable levels of IFs in Excel. How can we, then, accomplish the task? The VLOOKUP function comes in handy for this situation.5304790500833Range lookup (True): a value to look up falling in a range will be assigned a resulted value corresponding to the whole range of look-up value; one resulted value matched with a whole look-up range: “Look-up value in Range 1, gets result-value-1; look-up value in range 2, gets result-value-2” [Range lookup is default].Non-range lookup (False): match of a look-up value to a result value: 1-100Range lookup (True): a value to look up falling in a range will be assigned a resulted value corresponding to the whole range of look-up value; one resulted value matched with a whole look-up range: “Look-up value in Range 1, gets result-value-1; look-up value in range 2, gets result-value-2” [Range lookup is default].Non-range lookup (False): match of a look-up value to a result value: 1-1VLOOKUP function allows us to look up a value - numeric or text, such as a score (numeric) or a category (text) in a list (such as a score-grade correspondence table, or a member class-price correspondence table), and when a “hit” is found, return (assign) the value of the corresponding item in the “lookup table” (such as a letter grade or member-class price in the above two examples). The lookup table in our example below is shaded.The syntax for VLOOKUP function is:VLOOKUP(lookup_value, table_array, col_index_#, [range_lookup])40530922350 Look (cell), In (table) Return (column) Depend (type)0 Look (cell), In (table) Return (column) Depend (type)Explain:3802380262255Returned-value column #00Returned-value column #VLOOKUP(Cell to be looked up, lookup table, the # indicating the column in lookup table where the results of the lookup are listed, [true/false])center25301Lookup column0Lookup columnExample 1: ?ABCDE1NamePtsBonus/PenaltyPointsGrade2Ann71=VLOOKUP(B2,$D$2:$E$6,2)0F3Bob50=VLOOKUP(B3,$D$2:$E$6,2)60D4Cindy69=VLOOKUP(B4,$D$2:$E$6,2)70C5Dan88=VLOOKUP(B5,$D$2:$E$6,2)80B6Eric91=VLOOKUP(B6,$D$2:$E$6,2)90AFigure 3-1 Use VLOOKUP to determine rewards/penalties – range lookup2062163418465In range lookup, the values in this column are the BEGINNING of a range (0 is the beginning of Fire; 70 the beginning of $500 bonus, etc)00In range lookup, the values in this column are the BEGINNING of a range (0 is the beginning of Fire; 70 the beginning of $500 bonus, etc)The formula in, say, C5, means: “Look up the value in B5, from the table $D$2:$E$6, and if there is a ‘hit’, assign the value in the second (2) column of the lookup table $D$2:$E$6.”What does the last argument “True/False” mean? 10242471034890Also next page: right-6293900=VLOOKUP(B2,$D$2:$E$6,2, True) ---- (1)Or=VLOOKUP(B2,$D$2:$E$6,2, False) ---- (2)(1) is default, and (2) has to be explicitly specify. The difference? Usage: case (1) requires the first column of the lookup table to be in ascending order, while Case (2) does not, i.e, the first column can be in any order in situation (2).Meaning: Case (1) – when the value to be looked up FALLS IN A RANGE in the lookup table, a certain action/assignment – from 2nd column - is made; Case (2) when the val-to-be-looked-up has an EXACT MATCH with a value in the first column of the lookup table, an action/assignment – from 2nd column - is made.Example 2: Add one for membership class - price?ABCDE1NameTransactionIDTransaction/ChargeTransIDCharge2Ann1070=VLOOKUP(B2,$D$2:$E$6,2, False)1390$1,0203Bob2050=VLOOKUP(B3,$D$2:$E$6,2, False)2050$1,1004Cindy1165=VLOOKUP(B4,$D$2:$E$6,2, False)2288$3,2205Dan2288=VLOOKUP(B5,$D$2:$E$6,2, False)1169$2,1206Eric1391=VLOOKUP(B6,$D$2:$E$6,2, False)1070$520right285465 Look (cell), In (table) Return (column) Depend (type)0 Look (cell), In (table) Return (column) Depend (type)Figure 3-1 Use VLOOKUP to determine charges for transactions – non-range lookup23967941330232002496083518182440043559927153783300310114501313441001VLOOKUP(Val_to_look, Table, Column_to_retun, Range_or_not)918219299021500If the last argument (“4” above) is default (True), the 1st column [of lookup table] must be ascending – in that case it’s a range lookup, MEANING: the number/text to be looked up would fall into a certain range (in the lookup column – Column 1) the whole of which correspond to one value in the returned value column;If there’s such correspondence: 11-20 Action I21-30 Action IIWhat would 25 correspond to?If the last argument (“4” above) is False, the 1st column can be in any sequence – in that case it’s a non-range lookup, MEANING: the number/text to be lookup should EXACTLY match a certain value in the lookup column – Column 1, which (the fact of the match) will return one value from the returned value column;If there’s such correspondence: 11 M, 21 N, 31 O,What would 25 correspond to?The returned value column can be of any type (text, number, mix of the two)Examples: scores 700-750 is V Good; Sales over $10,000 has commission rate 2.5%The returned value column can be the 2nd, 3rd, … column in the lookup table (but NEVER, EVER, the 1st column!! You do not look up a value in a column and return the value itself!)1198230282971The lookup column is ALWAYS Column 1 ! ! Explore: HLOOKUP function (horizontal lookup) works in a similar fashion. You can try it yourself. The following is a section of a spreadsheet showing HLOOKUP: Row 1Row 2-306473127527800330454016319500140269016362700124175510195050032010351018540003201670839470001241755119736002017 New Notes:Syntax of VLOOKUP: VLOOKUP (Cell-to-look-up, Lookup-table, Return-column#, Range-or-non-range)Meaning:-14197612133400VLOOKUP (What-to-look-up, Where-to-look-up, Where-is-result, Range-or-non-range)32233273292000Argument 3 “Where-is-result” would NEVER, EVER be 1!!!!Section 3-2: Combination of IF and VLOOKUPOften times, one will encounter one of the following situations:1, A decision (IF) is to be made, when part of the data for the decision must be obtained from a lookup; - this would be an IF formula with VLOOKUP in the “condition” portion (remember IF formula has IF-THEN-ELSE? The “IF”-part is the “condition” part);2, A decision (IF) is to be made, when part of the “action/Assignment” value is provided by a lookup (similar to “1”, but now it is the THEN- or the ELSE-part that has VLOOKUP);3, A lookup is to be performed, when the lookup table changes according to some condition, in this case it is the second argument “Lookup Table” that will contain an IF;4, A lookup is to be performed, when the lookup result column changes according to some condition, in this case it is the third argument “Return-column-number” that will contain an IF.For each of the above scenarios, we have one example to demonstrate the treatment/handling of the situation:1, LA County Health Department: A decision (IF), with values for making the decision provided by a lookup. LACHD has contractors for government welfare programs. These contractors report to LACHD monthly for the amount of services (in $) they provided based on which the LACHD cumulates its annual total. At the year end, the contractors would report their year-end totals, which may have discrepancies from the totals LACDH has on hand. The discrepancies need to be identified.=IF (Cell >/</= VLOOKUP, Action_1, Action_2)4893163521845Did not design example yet0Did not design example yet2, Work-4-Us company’s employee reward program: A decision (IF), with the outcomes of the decision (THEN/ELSE) provided by VLOOKUP. W4U does well in its business that it has some money to reward its employees. Employees, based on their length of employment with the company, can choose from different paid vacations or paid off-days. =IF (Condition, VLOOKUP1_When_True, VLOOKUP_2_When_False)=IF (Condition_1, VLOOKUP1, IF(Condition_2, VLOOKUP_2, Other_Action) )3 & 4, LADWP: An account lookup, with the lookup table as well as the return-value column depending on what customer data is used to lookup. An LADWP customer calls in to find out her balance and last payment, for which she can provide either account number or phone number, and the LADWP’s system should be able to locate the customer’s records based on EITHER ONE of the data provided.=VLOOKUP( IF(condition_1, Use_Cell_1, IF(condition_2, Use_Cell_2, Use_Cell_3)),IF(condition_1, Use_Table_1, IF(condition_2, Use_Table_2, Use_Table_3)),IF(condition_1, Return_Column_x, IF(condition_2, Return_Column_y, Return_Column_z)) )Section 3-3: Workbook – Referencing across Worksheets1. A workbook contains multiple worksheets.2. Worksheets are identified with their names shown in the worksheet tabs. The name of a worksheet can be renamed in the same way as you rename a file.3. When you need to refer to a cell in ANOTHER worksheet, use the format in the following example (assuming you want to calculate total cost):Total Cost = Fixed cost + Volume * Variable CostIf each of the values is in the same cell address on different sheet, say B1 on sheet "FixedCost" stores fixed cost, B1 on sheet "Volume" stores volume, B1 on sheet "VariableCost" stores variable cost, B1 on sheet "TotalCost" is the formula that you are creating to calculate total cost, then you have in sheet "TotalCost", cell B1 the following formula:=FixedCost!B1+Volume!B1*VariableCost!B1 where the "!" means "sheet".You can do sum or average across sheets in a similar fashion, for example: =average(sheet1!g3, sheet2!g3, sheet3!g3) or =average(sheet1:sheet3!g3)Section 3-3: Conditional FormattingXYZ Company recently had some customer complaints on the quality of some products they produced. XYZ Company believes that their production processes are in normal performance; but to be sure, they want you to run some statistical analyses to verify that. Assume that you decide to run a t-test to determine whether the quality indicators of a wide range of products are significantly different between Q1 and Q2. You can use the feature of conditional formatting in Excel (and all other electronic spreadsheets) to find those values less than the specified p value. Steps to perform conditional formatting:1. Highlight the cell range that you want to format;2. On the ribbon, select Home ribbon, Styles group, then Conditional Formatting.3. You can then select from the drop-down list the logical relationship you need. Here you want to select less than. Then you want to fill out the value(s) in the slot(s). Here you only have one value, which is 0.05. Fill it in the first slot. (What if you want to set the condition “between 0.05 and 0.1”? Think and try)Click the Format button, and you will see a Format Cells dialog box pop up. You can then choose the format you want, such as red color. Click OK, and see this: Section 3-4: Sorting a SpreadsheetIntelliImage, a company selling digital camera, digital camcorder (DV), and scanners, has four sales regions - Las Vegas, Los Angeles, Phoenix, and San Francisco. The following table shows the digital camera and DV sales of IntelliImage in the four sales regions. ABCD4Sales ($million)Digital CameraDVCity Totals5Las Vegas209296Los Angeles1411257Phoenix83118San Francisco187259Product totals603090The VP for Marketing of IntelliImage would like to see the sales regions listed according to their sales performance, highest first (descending). Here is what you would do:(1) Highlight A4:D8 – data, city totals, and the column headers, but NOT “Product totals”: you want to leave the product totals as the last row.(2) Select Data on menu bar, select Sort, and a sort dialog box will appear as in the figure below. (3) You want to sort by City Totals. Click at the down-arrow button at the sort by box and a drop-down list of fields would allow you to choose City Totals you want to sort on.342900033782000(4) If you want to sort on a different field when there are more than one row having the same city total, you can then choose a field in the Then by box. This would be your secondary sorting field.45720089408000 Sort by City Total(5) Click OK and your data is sorted!(6) Similarly, you can sort on other fields such as Digital Camera or DV.SUMMARY on VLOOKUPVLOOKUP Syntax:VLOOKUP(Argument 1,Arg 2,Arg 3,Arg 4)VLOOKUP(Value to look upLookup table121793017780053839914367500Column # (in the table) to get the valueRange lookup – True/False)1200356-2057850T (or 1) -Range LookupF ( or 0) -Non-Range LookupCan be omitted, in which case it is the default value – True (range lookup)Item #4Can be omitted (default = True)Must be explicitly indicated: False1stcolumn in lookup tableMUST be ascendingCan be any orderHow it worksThe look-up value falls in a given range on the lookup columnThe look-up value has an exact match?on the lookup column“Hit”?and resultfalling in rangeexact matchreturn the value of the?indicated column in argument #3 - Item #3 can indicate ANY column! (2, 3, …), but NEVER be 1! ! ! NEVER! ! !Interpre-tationMatch of a value to its “range” – if you are between range_begin and range_end (such as 70 and 79), you all get the same match (such as grade C)Match of a value to a value – - if you are a specific a, you would be matched with a specific x; - if a specific b, matched with specific y; - if your lookup-up value is NOT ON Col #1 of the lookup table, you would not get a result!Examples of correspond-ence:- scores to grade;- credit scores to credit rating;- ages to age group;- taxable incomes to tax bracket- points earned to membership class- name to address;- state to capital;- year to Oscar award movie;- transaction number to charge- stud ID to student DOB-8367-102240Think about these scenarios; you may see them in exam/project00Think about these scenarios; you may see them in exam/project ................
................

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

Google Online Preview   Download