The following questions ask you to use the following spreadsheet to create a formula for the "Year End Bonus" column. Each question asks you to create the formula based on different rules. For each question, assume that the formula is in cell H2 and then copied down to the other cells in column H.

 


NOTE: The formula to calculate "Years With Company" in cell C2 is the following:       
                        =YEAR(TODAY())-YEAR(B2)

 

  1. Write a formula for "Year End Bonus" that calculates the bonus using the following rules:

    1. If the person is with the company at least 5 years OR the person has more than $1500 in total annual sales then the year end bonus is 20% times their total sales.

    2. Otherwise the bonus is 10% times their total sales.


  2. Write a formula for "Year End Bonus" that calculates the bonus using the following rules:

    1. If the person is with the company at least 5 years

      OR

      the person has at least $1500 in total sales AND there is no quarter in which the person sold less than $100 the bonus is 20% times their total sales.

    2. Otherwise the bonus is 10% times their total sales.

 

  1. Write a formula for "Year End Bonus" that calculates the bonus using the following rules:
    (HINT: for this one you will need to use BOTH logical functions and a nested IF function.)

    1. Bonus is 30% of total sales if the person is with the company at least 5 years AND has at least $1000 in total sales

    2. Bonus is 20% of total sales if the person is with the company at least 2 years  OR they sold at least $1500

    3. Otherwise the bonus is 10% times their total sales.