Commission Formula

Evening, 

Hope you're all well.

I’m hitting a brick wall inserting a formula into a tracker for my sales teams commission structure which is being redone.

It’s a tiered system whereby the salesperson will get commission in brackets until they reach target, and then a higher percentage on anything over target; (0.1% for the first 20%) of their target, (0.2% on the next 20%), (0.3% on the next 20%) so on. 

Until they reach target, at which point everything over target they are rewarded at 2%. 

If they are under target, they will still receive commission, but only within the brackets. 

If anyone who’s done a similar structure that would be able to take a look and let me know their thoughts, it would be really appreciated, as I seem to be going around in circles.

I've extracted a section and published it below.

Thanks in advanced, any suggestions welcome :D


Cheers,

Glen.

Tags:

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 01/25/21 Answer ✓

    Hi @Paul Newcome @Glen Urquhart 

    Hope you are fine, i do it using the following formula, please confirm if this ok for you or we must change the factors of calculation.

    =IF([Month End Sales Actual]@row > [2021 Target]@row, (0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + 0.003 * [Third 20%]@row + 0.004 * [Last 20%]@row + ([Over Target]@row * 0.02)), (IF(AND([Month End Sales Actual]@row <= [Last 20%]@row, [Month End Sales Actual]@row > [Third 20%]@row), ((0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + 0.003 * [Third 20%]@row + (([Month End Sales Actual]@row - [Third 20%]@row) * 0.004))), (IF(AND([Month End Sales Actual]@row > [Second 20%]@row, [Month End Sales Actual]@row <= [Third 20%]@row), ((0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + ([Month End Sales Actual]@row - [Second 20%]@row) * 0.003)), (IF(AND([Month End Sales Actual]@row > [First 20%]@row, [Month End Sales Actual]@row <= [Second 20%]@row), (0.001 * [First 20%]@row + ([Month End Sales Actual]@row - [First 20%]@row) * 0.002), (IF(AND([Month End Sales Actual]@row > Divider@row, [Month End Sales Actual]@row <= [First 20%]@row), 0.001 * ([Month End Sales Actual]@row - Divider@row))))))))))



    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!