HW – Nested Ifs

 

Using the following spreadsheet:

 

 

Question 1:

 

Write a formula to be placed in cell C2 and copied to cells C3 through C5 to calculate the commission using the following rules:

 

  1. If the name is “Mary” (she is the boss’ daughter) the commission is 50% of the sales.
  2. If the name is not Mary and the salesman is the best salesman (i.e. most sales) 40%
  3. If the name is not Mary and the salesman is the worst salesman (i.e. most sales) 10%
  4. If no other rules apply: 30%

 

Question 2:

 

Modify the above formula to use the following additional rule (all the other rules still apply)

  1. If the name is not Mary and the person is NOT the worst salesman but IS BELOW AVERAGE then 20%.