Hi,
I am working on a commission project. I have tiered commissions that I need to calculate weekly. And of course should an associate obtain 15 vs. 10 in week 3 they will start attaining a higher payout for 5 of those. I need a formula that will allow me to pull from the tiered commission table based on what's being entered manually into the weekly progress columns. And I need it to calculate cumulatively for weeks going forward so throughout the month as they progress closer to their monthly goals or for some teams quarterly goals the formula automatically adjusts.
Please let me know if you need more info. Thank you so much!
Pulling from this table
Thanks again so much for your help
I am working on a commission project. I have tiered commissions that I need to calculate weekly. And of course should an associate obtain 15 vs. 10 in week 3 they will start attaining a higher payout for 5 of those. I need a formula that will allow me to pull from the tiered commission table based on what's being entered manually into the weekly progress columns. And I need it to calculate cumulatively for weeks going forward so throughout the month as they progress closer to their monthly goals or for some teams quarterly goals the formula automatically adjusts.
Please let me know if you need more info. Thank you so much!
Salesperson | Whole Life | Term Life | W/e 5/7 Commission (current formula) |
1 | 1 | =(IFS([@[Whole Life]]<=A$61,[@[Whole Life]]*C$61, AND(A$61<[@[Whole Life]],[@[Whole Life]]<=A$62),[@[Whole Life]]*C$62, AND(A$62<[@[Whole Life]],[@[Whole Life]]<=A$63),[@[Whole Life]]*C$63, AND(A$63<[@[Whole Life]],[@[Whole Life]]<=A$64),[@[Whole Life]]*C$64, AND(A$64<[@[Whole Life]],[@[Whole Life]]<=A$65),[@[Whole Life]]*C$65, A$66<=[@[Whole Life]],[@[Whole Life]]*C$66))+(IFS([@[Term Life]]<=A$61,[@[Term Life]]*I$61, AND(A$61<[@[Term Life]],[@[Term Life]]<=A$62),[@[Term Life]]*I$62, AND(A$62<[@[Term Life]],[@[Term Life]]<=A$63),[@[Term Life]]*I$63, AND(A$63<[@[Term Life]],[@[Term Life]]<=A$64),[@[Term Life]]*I$64, AND(A$64<[@[Term Life]],[@[Term Life]]<=A$65),[@[Term Life]]*I$65, A$66<=[@[Term Life]],[@[Term Life]]*I$66)) | |
1 | 1 | 132 | |
1 | 1 | 132 | |
1 | 1 | 132 |
Pulling from this table
Total Apps/Month | 1st Fee |
19 | |
29 | |
39 | |
49 | |
64 | |
65 |
Thanks again so much for your help