Chapter3 (1).doc

21 Pages
Unlock Document

Electrical Engineering and Computer Science
EECS 1520
Nicole Nivillac

CSE 1520.03 The Glade Computer Use: Fundamentals Laboratory Handbook Chapter 3: The IF Function and Table Lookup Objectives This laboratory focuses on the use ofIF and LOOKUP functions, while continuing to introduce other functions as well. Here is a partial list of what the lab covers: • Further practice of good design techniques • More practice using logical functions • Creating and using lookup tables and functions Preparation To be able to complete this laboratory in about 3 hours it is essential that you are properly prepared. You should read the whole of this lab very carefully. Introduction This lab continues the examination of logical functions and expands on techniques that allow a choice to be made between which values to use in a calculation. One of the common ones is the LOOKUP function. Exercise 1 - Sales Person Bonus Model Open the fileExercise 1in Support Files(Chapter 3) on the course website. This model has two worksheets – theComments worksheet and the Sales_Record worksheet. Your aim is to add a column to the Sales_Record worksheet, which identifies whether the particular sales person has made sales less than the average of all sales or equal to or greater than the average. The cells in the new column might containEqual/Above or Below , for example. To do this you must first calculate the average of the sales. Enter a label sSaless Average in a cell at the bottom of the column of names and in the cell next to it compose the formula that will calculate the average of the sales. You’ll need toAVERAGEe function, which you have seen in a previous chapter. If you name the Sales column your formula will simply look like this: =AVERAGE(Sales) Next enter a heading for the new column – perhaCompared to Average . Make sure the heading is appropriately formatted for the cell width and also by font size etc. You’ll probably want to choose, in Excel 2003 thWrap text option from the Alignment tab in the Format Cells window (Figure 3.1) or, in Excel 2007 theHome tab and Wrap Text 3-1 The Glade Computer Use: Fundamentals Laboratory Handbook within the Alignment group, and to increase the cell height for the row that contains the column headings. Figure 3.1 – the Alignment tab in the Format Cells window The IF Function Now you need to enter the formula that will decide whether the words Below or Equal/Above will appear in the first cell. Select that first cell and click on the Function button in the tool bar: The Function button: The IF function can be found in the Logical group of functions (see Figure 3.2), and after pressing the OK button the argument specification panel shown in Figure 3.3 appears. Figure 3.2 – pasting the IF function Figure 3.3 –argument panel 3-2 CSE 1520.03 The Glade Computer Use: Fundamentals Laboratory Handbook The Logical_test argument needs to compare the sales figure (for this sales person) to the average sales. If you didn’t name the average sales value that you calculated recently then cancel this operation and go back and define a name for it. The logical test that you enter here (using the names you have defined) will be: Sales < Average_Sales In Excel 2003, you can enter this using the Name/Paste selection from the Insertmenu, except that you’ll have to type the < symbol. In Excel 2007, you can use the Use in Formulas command in the Defined Names group within the Formulas tab. Note : (Excel 2003 only) After you have typed the < symbol you'll find that clicking on the Insert menu to try to use Name/Paste for Average_Sales does not work. The menu does not appear. This seems to be a bug in the Excel program. To get around it simply click in one of the other text boxes and then click back in the Logical_test text box and you'll find that the menu works again. You'll encounter this problem frequently in this and future labs so take good note of it. The value of thisLogical_test expression should not be thought of as a number (although it might be represented as a number by the computer), nor should it be thought of as a word. Instead you should think of its value as the abstract true or false – either it is true that sales is less than the average, or it is false (sales is not less than the average). Thinking in this way is key to constructing the argument correctly. The Value_if_true argument should simply be the text “ Below ” – so type this (with double quotes, “….”) into the text box now, and then the string “ Equal/Above ” into the Value_if_false text box. Press the OK button and you should find that the formula has chosen the string Below as the value for this first cell, because the sales amount for salesperson Bushby is in fact less than the average sales. Fill the formula down the column to see the complete results. The first few rows of thSales_Record worksheet should look like Figure 3.4. Figure 3.4 – part of the Sales_Record worksheet 3-3 The Glade Computer Use: Fundamentals Laboratory Handbook Adding a Bonus Let’s add one further step to this. Suppose that we want to calculate a bonus of 10% of sales for those who achieved sales of at least one standard deviation better than the average. A standard deviation describes how values in a sample are distributed about the average. Typically 68% of values fall within one standard deviation above or below the average. So if a salesperson sells more than “the average plus one standard deviation” they have done very well compared to others in the group. The standard deviation can be calculated using a function just like the average was. The steps required here include labeling a cell for the standard deviation, creating the formula to calculate the value, labeling a new column for the bonus, and creating the formula to calculate those values. Note that if the sales for a particular person is less than the average plus one standard deviation the bonus cell would best be left blank. In the next row under the Sales Average enter a label such asStandard Deviation: and in the adjacent cell create the formula to calculate the value. The function is calledSTDEV and can be found in the Statisticalgroup of functions. Next enter a column heading – Bonus – and format it appropriately. The formula for the Bonus column will again use an IF function. This time however you need to implement the arguments as follows: Logical_test: sales > sales average + one standard deviation Value_if_true: 10% of sales Value_if_false: empty These arguments are written in normal English – they do not use the cell names and symbols that you need to use in implementing the formula. You cannot write 10% of sales in the text box for Value_if_true for example – you must translate it first into the correct symbols and names that the spreadsheet recognises. The easiest way to implement the Value_if_false argument is to type two double quotes (“”). This makes the cell appear to be empty, although it’s not quite the same as actually being empty. You should be able to figure out how to implement the other arguments yourself. The first few rows of the worksheet should look like Figure 3.5. 3-4 CSE 1520.03 The Glade Computer Use: Fundamentals Laboratory Handbook Figure 3.5 – part of the Sales_Record worksheet with Bonus included Exercise 2 - Kasch Pulse Recovery Study Open the file Exercise 2 inSupport Files (Chapter 3) on the course website. You’ll see a model with two worksheets – a Comments worksheet and a Fitness_Data worksheet. It contains a list of names, along with associated gender, age, and pulse rate measurement. The pulse rate is measured after 5 seconds of rest following 3 minutes of exercise and it is an indication of the fitness level of the individual. Your aim here is to add a new column identifying the fitness level of the subjects in the study. Part ofFitness_Data worksheet with this column added is shown in Figure 3.6. Figure 3.6 – part of the Fitness_Data worksheet 3-5 The Glade Computer Use: Fundamentals Laboratory Handbook Ex 2.1 - Two Fitness Ratings One simple approach is to ignore age and gender and to state that if the pulse rate is under 95 the fitness level is good, otherwise it is poor. You should be able to implement this yourself. Enter a column title and an IF function formula that calculates eitheGood or Poor as the values for the cells in the column. Ex 2.2 - Many Different Ratings Actually it is better to identify levels of fitness – say Excellent, Good, Average, Fair, and Poor. Ignoring age and gender you could define these levels according to the following table: Pulse Rate Fitness Rating Less than 80 Excellent 80 to <90 Good 90 to <105 Average 105 to <115 Fair 115 and greater Poor To implement this using an IFfunction requires careful analysis. TheIF function template require three arguments – a logical test, the value to return if the test is true, and the value to return if the test is false. In this example there is not just one logical test – there are 5. We could begin by saying I F(Pulse_Rate < 80 , “Excellent”, ???? ) . This is certainly correct so far, since if the pulse rate is indeed less than 80 we do want the woExcellent to appear in the cell. The question remains as to what to use for the Value_if_false argument. In other words, what is the answer if the pulse rate is greater than or equal to 80? Well, we don’t know the answer without further testing – so we must replace the Value_if_false argument with another IF function: IF(Pulse_Rate < 80, “Excellent ”,IF(Pulse_Rate < 90, “Good ”, ???? ) ) Now we have the answer if the pulse rate is less than 80, and if it’s greater than or equal to 80 we also have the answer if it’s less than 90. But if the pulse rate is greater than 90 we still don’t have the answer. Clearly, we need to continue this process of building what is called a “nested”IFfunction. To implement this formula, first name the Pulse Rate column and then use the function wizard to start creating the nested IF formula. You should be easily able to enter the correct specification for the first Logical_test and Value_if_true text boxes. For the Value_if_false text box you want to enter another IF function. The easiest way to do this 3-6 CSE 1520.03 The Glade Computer Use: Fundamentals Laboratory Handbook is to click on the name box (which is in the top left just above the argument panel and should be displaying IF). You should get a new IFfunction argument panel, as shown in Figure 3.7. Figure 3.7 – the first nested IF function Observe that a new IFfunction has been entered in the formula under construction in the formula bar. This new IF function has no arguments at this time, but as you specify the arguments in this panel you’ll see them inserted into the formula in the formula bar. You need to enter Pulse_Rate < 90 as the Logical_test and “ Good ” as the Value_if_true. For the Value_if_false you need to enter another IF function. Your formula should now look like this and another empty argument panel should be displayed. Notice that the logical test in the second IF function does NOT say: 80 <= Pulse_Rate < 90 Beside the fact that this is illegal in Excel, it is also unnecessary because the preceding test accounts for the first part, i.e80 <= Pulse_Rate . If the logical test in the firsIF function is true the answer is given as Excellent, and none of the other IF functions are carried out. Thus, if the second IF function is examined it must be because the first one was found to be false! This means that Pulse_Rate must definitely be greater than (or equal to) 80 and hence that first part of the logical test80 <= Pulse_Rate ) is redundant because it is already known to be true. The method of writing nested IF functions that we are using here only works if you write the logical tests in order – increasing order of pulse rate in this case. 3-7 The Glade CSE 1520.03 Computer Use: Fundamentals Laboratory Handbook Continuing with the construction of nested IF functions, you’ll end up with the formula in Figure 3.8, in which the lasValue_if_falseargument is yet to be specified. Figure 3.8 – the final nest IF function argument panel You could nest another IF function with the logical test Pu lse_Rate >= 115 and the Value_if_true set to Poor”. In this case thValue_if_falsecould be left unspecified or set to “”. It is better to realise that having dealt with the cases of pulse rate <80, <90, <105 and <115 all values that are left must be greater than 115 and hence rate “ Poor ”. The Value_if_false argument for this lasIF function can simply be specified as Poor ” rather than another function. Complete the formula and fill it down the column to see that it works correctly. Ex 2.3 - Dependence on Gender Age dependence is left as an exercise at the end of this chapter, so let’s first see how the gender dependence can be included in the Fitness Rating. The following table describes the fitness ratings for males and females: For males: For females: Pulse Rate Fitness Rating Pulse Rate Fitness Rating Less than 80 Excellent Less than 87 Excellent 80 to <90 Good 87 to <100 Good 90 to <105 Average 100 to <111 Average 105 to <115 Fair 111 to <123 Fair 115 and greater Poor 123 and greater Poor 3-8 CSE 1520.03 The Glade Computer Use: Fundamentals Laboratory Handbook To implement a calculation such as this implies we first need to say something like: If (Gender is male, true value is computed as for a male, false value is computed as for a female) This means that the Value_if_true argument will be a series of nestedIFfunctions which use the table for males and the Value_if_false argument will be a series of nested IF functions which use the table for females. To do this you can modify the formula that you wrote in the previous part. The first thing to do however is to define a name for the gender column that you will use in the formula – so do that now. To edit the previous formula select the top cell and click just after the = symbol in the formula bar. This is where you are going to start typing the new parts of the formula. A vertical line should be blinking between the = symbol and the I of IF, indicating where new characters that you type will appear. Assuming Gender is the name you defined for theGender column type the following: IF(Gender= “M ”, The existing series of nested IFfunctions constitute theValue_if_true argument of this new IF function you are inserting. To create theValue_if_false argument you can copy and paste the Value_if_true argument and then change the numbers in the Logical_test arguments to match the values in the table for females. Carefully select from IF(Pulse_Rate<80 right to the end of the existing formula and choose, in Excel 2003, Copy from the Edit menu, or, in Excel 2007, Copy from the Clipboard group within the Home tab. Next click right at the end of the existing formula (i.e. after all of the parentheses) and type a comma. If you type the comma while the selection you just copied is still highlighted it will all be replaced by the comma, so take care with this. Now, in Excel 2003, choose Paste from the Edit menu, or, in Excel 2007, Paste from the Clipboard group within the Home tab, and the series of nestedIF functions should appear again in the formula. Now change 80 to 87, 90 to 100, 105 to 111 and 115 to 123 as implied by the table above. 3-9 The Glade CSE 1520.03 Computer Use: Fundamentals Laboratory Handbook Finally type a right parenthesis ) at the very end to close the argument list for the new IF function you have inserted. The formula should look something like this, though this one has been formatted to make it easier to read: IF(Gender = ”M” , IF(Pulse_Rate < 80, “Excellent”, IF(Pulse_Rate < 90, “Good”, IF(Pulse_Rate < 105, “Average”, IF(Pulse_Rate < 115, “Fair”, “Poor” ) ) ) ) , IF(Pulse_Rate < 87, “Excellent”, IF(Pulse_Rate < 100, “Good”, IF(Pulse_Rate < 111, “Average”, IF(Pulse_Rate < 123, “Fair”, “Poor” ) ) ) ) ) Examine this formula very carefully to make sure that you understand all of its components. You should develop flexibility in how you build formulas – using the point and click method at times and at others just typing, or cutting and pasting. Commentary As you can see formulas can get quite complicated if you attempt to combine many of them. This is not a good practice and is done here mainly to demonstrate that it is confusing. There are better ways to tackle this kind of problem, as you’ll soon see. Exercise 3 - Sales Discount Model This section extends our study of IF functions. In particular it introduces the idea of a compound logical test involving the use of thAND and OR functions. Open the fileExercise 3 in Support Files (Chapter 3) on the course website. You’ll see a Comments worksheet (read it carefully) and a Discounts worksheet, which contains the codes for various products sold in a store, the status of the product (C means current and D means discontinued), the quantity of the product in stock, and the average daily sales for the product. Ex 3.1 As the store manager you want to hold a sale in order to attract customers and sell off old stock that is either discontinued or not selling very well. A product is not selling very well if there is so much in stock that at the average daily sales the stock would last for 15 days or more. If a product is discontinued or not selling well you decide to discount its price by 25%. You’ll discount everything else by 10%. So the task now is to implement the criteria just described so that you have a new column showing either 10% or 25% as the discount percentage for each stock item. 3-10 CSE 1520.03 The Glade Computer Use: Fundame
More Less

Related notes for EECS 1520

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.