Decisions

• For this exercise we will create a marketing strategy for a game club.
• We will start with this workbook
• The club has 20 members and we have the following data
  • A name.
  • The number of members in their family
  • If the membership is a renewal or not.
  • The credit rating for the person.
• There is also a set of constants on the Data Tables worksheet.
  • Renewing members receive a discount, this is listed in B3.
  • If you owe more than a fixed amount, you can make monthly payments, the minimal amount is listed in B4.
  • Financing is based on a credit rating, this table is in a6:b10.
  • Membership is based on the number of people in the family , this table is in a12:c16.
• Return to the Member List worksheet.
• Using the membership table, and the number in each family we want to compute both the membership type and the base fee.
  • This will be done with the vlookup function.
    • This function takes a value, in this case the number of members in a family
    • And a table, where the value will appear somewhere in the first column
    • And can return values from corresponding columns.
    • It is vlookup because the table is in columns.
    • The hlookup function is the same but the table is in rows.
  • For Guy Shelton
    • He has two members in his family, so the membership type is a double.
    • And the fee is $150.
  • We could type these in by hand but
    • If we need to adjust fees based on the membership, we would have to retype it.
    • And I might give you 1000 members, because I am mean.
  • For cell c2 we are looking for the data in cell b2 in table A14:c16 (don't include the headings) and we want the second column back
    • So use the formula =vlookup(b2, 'Data Tables'!A14:c16,2)
    • There as another parameter we will discuss later but this will do because we are breaking the data into bins
    • This is called an approximate match.
      • The first column needs to be in order
      • And excel will match the first number less than or equal to the values in the column.
  • We want to copy this down the column, so what should we make a constant reference? (The table)
  • The formula becomes =vlookup(b2, 'Data Tables'!A$14:C$16,2)
  • Copy this formula down.
• The same is true for the Base Cost
  • but in this case we want the third column back, not the second.
  • The formula becomes =vlookup(b2, 'Data Tables'!A$14:C$16,3)
• Let's skip to the APR column
  • This is another table lookup
  • But in this case we need an exact match, not a bin match.
  • So we use the same formula, but add a fourth parameter
  • =vlookup(I2, 'Data Tables'!A$8:B$10,2,false)
• This is critical you understand the difference between an exact match and an approximate match.
• If is a second function that allows us to ask a yes-no question and take an action based on it.
• For cell F2 we need to decide what amount of discount to apply to our members.
  • If the e2 contains a Y, the discount is 15%, otherwise it is 0
  • The yes no question is does e2 contain a y (or e2="Y")
  • If the answer is yes, the discount is D2 * B3 on the data table
  • If the answer is no, the discount is 0
  • The if function
    • The yes-no question
    • A formula to compute the value if the answer is yes
    • A formula to compute the value if the answer is no
  • In our case =if(e2="Y",d2*'Data Tables'!B$3, 0)
• We can now compute the total price, =d2-f2
• For column h, we want to add "Yes" if the amount is high enough and a "No" if not.
  • This is another if.
  • yes-no c2>b4
  • Thing to do if yes - "Yes"
  • Thing to do if no - "No"
  • =if(g2>'Data Tables'!B$4,"Yes","No")
• We can now finish the table.