I have table of Sales reps and corresponding bonus structure for each person. Each rep is payed a bonus based on amount of sales. The bonus for reach Tier 1 is $5,000; the bonus for reaching Tier 2 is $50,000. The issue is each rep must achieve a different level of total sales to reach tier 1 and tier 2. I have attached a sample spreadsheet with actual sales for each rep and expected bonus for each. Thanks in advance for any comments.
hi maacmaac. it seems like Tier1 is 10,000 instead of 5,000? or is there something i dont understand? if you need to return the tier, try this in H2:
=IF(G2>=C2,2,IF(G2>=B2,1,0))
then in I2:
=CHOOSE(H2+1,0,10000,50000)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hello,
You can use this for column I
and for column H, I suggest using benishiryo's method, that's a really good one
Please Login or Register to view this content.
And here's the sample.Please Login or Register to view this content.
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
That works fine with the Sumproduct. Is there a way to eliminate having to use column H ("Tier Reached") and incorporate into column I? Column H was only there for illustrative purposes and not part of actual spreadsheet model? Attached is updated example. Thanks for all comments.
Well, you can put straight the formula in column H into the CHOOSE, like
Here is your ver 03 with the formula.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks