I need to calculate the Rebate % earned based on two criteria: sales volume & compliance ratio - the 3 columns I'm trying to complete are in red: Tier, Rebate % & Rebate Amount.
Question: What is the rebate amount for customer 121761 for September? Sales Volume is over $100K and Compliance is over 90%, so the rebate % earned would be 24%.
Matrix:
<tbody>
</tbody>
Data:
<tbody>
</tbody>
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Proposed Formulas:
<colgroup><col><col></colgroup><tbody>
</tbody>
Format for the Tier column return value:
<colgroup><col></colgroup><tbody>
</tbody>
Question: What is the rebate amount for customer 121761 for September? Sales Volume is over $100K and Compliance is over 90%, so the rebate % earned would be 24%.
Matrix:
Compliance Ratio | |||||
Sales Volume | 0% | 70% | 80% | 85% | 90% |
$ 0.0 | 5% | 7% | 10% | 15% | 19% |
$ 15,000.00 | 6% | 8% | 11% | 16% | 20% |
$ 30,000.00 | 7% | 9% | 12% | 17% | 21% |
$ 50,000.00 | 8% | 10% | 13% | 18% | 22% |
$ 75,000.00 | 9% | 11% | 14% | 19% | 23% |
$ 100,000.00 | 10% | 12% | 15% | 20% | 24% |
<tbody>
</tbody>
Data:
<tbody>
</tbody>
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>
Start Date | End Date | Customer | Agreement | Sales Volume | Compliance Ratio | Tier | Rebate % | Rebate Amount |
9/1/2018 | 9/30/2018 | 121761 | 47422 | $ 109,537.65 | 1.002 | |||
9/1/2018 | 9/30/2018 | 80437 | 39494 | $ 50,194.53 | 0.930 | |||
9/1/2018 | 9/30/2018 | 638392 | 39903 | $ 60,340.24 | 0.750 | |||
9/1/2018 | 9/30/2018 | 77701 | 39476 | $ 146,524.56 | 1.010 | |||
10/1/2018 | 10/31/2018 | 375991 | 39476 | $ 360,951.95 | 0.930 | |||
10/1/2018 | 10/31/2018 | 39449 | 39494 | $ 469,241.43 | 0.895 |
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Proposed Formulas:
Tier | =IF(F2>0.9,"90%+",IF(F2>0.85,"85-89.99%",IF(F2>0.8,"80-84.99%",IF(F2>0.7,"70-9.99%","0-69.99%")))) Is there a cleaner formula to pull in the tier range? |
Rebate % | Need help with formula |
Rebate Amount | =H2*E2 |
<colgroup><col><col></colgroup><tbody>
</tbody>
Format for the Tier column return value:
Tier Range |
0-69.99% |
70-79.99% |
80-84.99% |
85-89.99% |
90%+ |
<colgroup><col></colgroup><tbody>
</tbody>