Tiered Commission Calculation

ctbcs used Ask the Experts™
I am trying to develop a way to calculate commission based upon a sales goal percentage.  For instance, if the sales person meets less than 50%, they would receive 0% of Margin.  If the sales person receives 50% they would receive 10% of Margin.  If the sales person receives 65% they would receive 20% of Margin.  If the sales person receives 80% they would receive 30% of margin.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Using conditional tests to output the correct multiplier would be the way to go
Using bested conditional if >65%, multiplier, if >50% ,multiplier, if <20%,0,0

Your not description on the setup I.e. If you are using vba, or it is a function of having a column tracking.....or how the data analysis is performed.
You could use a lookup table like attached example.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial