Nested IF and LOOKUP Functions for Fitness Rating and Sales Analysis

1. EECS 1520 -- Computer Use: Fundamentals Glade Manual – Chapter 3 • Nested “IF” function: e.g. In exercise 2:Kasch Pulse Recovery Study - We want to identify the “Fitness Rating” for a list of people based on their “Pulse Rate” readings: The IF 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!

2. EECS 1520 -- Computer Use: Fundamentals Glade Manual – Chapter 3 • Nested “IF” function: 0 80 90 105 115 Excellent Good Average Fair Poor True False True False True False True False The logical implementation would be: if(Less than 80, “Excellent”, if(80 to <90, “Good”, if(90 to <105, “Average”, if(105 to <115, “Fair”, “Poor”)))) Nested “if”

3. EECS 1520 -- Computer Use: Fundamentals Glade Manual – Chapter 3 • “LOOKUP” function: - Instead of using “Nested-if” function for the above scenario, the “LOOKUP” function can be used to simplify the implementation - In Excel: “=LOOKUP(lookup_value, lookup_vector, result_vector)” - There are 3 items need to be referred to. Original data Result Length of lookup_vector = length of result_vector Lookup table worksheet Original data worksheet

4. EECS 1520 -- Computer Use: Fundamentals Glade Manual – Chapter 3 • “LOOKUP” function: - In exercise 5, you are asked to determine the “Fitness Rating” of a list of people depends on their gender. The basic idea is to say: If (subject is male, lookup the table for males, lookup the table for females)

5. EECS 1520 -- Computer Use: Fundamentals Glade Manual – Chapter 3 • Exercise 3 (Sales Discount Model): • This exercise also introduces the idea of a compound logical test involving the use of the “AND” and “OR” functions • In this exercise: If ( it’s a women’s clothing item AND it’s not selling well), discount by 25% , otherwise no discount The basic idea is to say: IF (it’s a women’s clothing item AND it’s not selling well) Value_if_true: discount by 25% Value_if_false: no discount Logical 1 Logical 2

6. EECS 1520 -- Computer Use: Fundamentals Glade Manual – Chapter 3 • Exercise 3 (Sales Discount Model): Logical 1 Logical 2 IF (it’s a women’s clothing item AND it’s not selling well) Value_if_true: discount by 25% Value_if_false: no discount Note: “women’s clothing” is labeled as “CW” in the “Product Code” Logical 1: are the first two characters of product code = CW? Logical 2: “not selling well” means “Stock_Quantity > 15*Average_Daily_Sales”

7. EECS 1520 -- Computer Use: Fundamentals Glade Manual – Chapter 3 • Homework 3: • Exercise 1 (Sales Person Bonus Model) [formula view] • Use “IF” function to determine the “Bonus” of a salesperson • Exercise 3 (Sales Discount Model) [formula view] • Use “AND” and “LEFT” (i.e. from the Text group of functions) functions in a “IF” logical statement to determine which “clothing – women’s” items need to give a discount of 25% • Exercise 5 (Kasch Pulse Recovery Study Again) [formula view] • Use “IF” and “LOOKUP” functions to determine the “Fitness Rating” for both men and women