Posts by saint3232

Looking for a formula for a zero based commission structure. I am having trouble with the formula. I have attached a breakout of what I need and an explanation of the end goal. Thanks in advance for any help you can provide.

Re: Zero Based Commission Formula

Thanks Cringe2.

What I am looking for is a zero based calculation tool to demonstrate output.

I want to able to put any amount into the net services cell and have the correct answer come out in the top cell directly under Breakout.

for example in set #1: If an employee earned 1600.00 and the scale was setup as such 0-1500 then they would get 40% of any amount less then 1500.

0-2000 then they would get 45% of any amount less then 2000, etc.

Thanks from a grateful yank!

Looking for a formula for a 3 tier and 4 tier zero based commission structure.

Re: Step Based Commission Formula

It worked! Thank you very much. I appreciate your continued effort.
Thanks also for the "HTH" lingo. I am still a bit of a novice in this forum.

I asked for help before but was not able to articulate what I was looking for. I have attached the physical document this time.

Please open and enter the first worksheet labeled "hair model". Scroll down to the commission scale highlighted in "Yellow". The net commission and the answer are there, what I am looking for is the formula. Thanks in advance.
john

Re: step based commission formula

Hi HTH,

Thanks for the post. However that's not exactly what I am looking for. When I inserted the formula you supplied the answer did not come out right.
Here is what I have so far. I know that I am missing something simple. Could you take a look to see if you can figure out where I am going wrong?

=IF(C25<=500,C25*0.40,IF(AND(C25>500,C25<=750),(200+(C25-750)*0.45),IF(AND(C25>750,C25<=1000),200+113+(C25-1000)*0.47),IF(AND(C25>1000,C25<10000),200+113+113(C25-10000)*0.5,"oops"))))

Re: step based commission formula

Hey Ross,

Thanks a bunch. However I think there is supposed to be a another piece to the code. I am not sure I explained myself well. For example;

If a service provider makes $1500 for the week and they are on a stepped scale then it would break out accordingly.

on the first $500 They would be paid 40%
on the $500 up to $750 they would paid 45% (on that $250)
on the $750 up to $1000 they would be paid 47% (on that $250)
on the remaining amount above $1000 up to $9999.99 they would be paid at 50%.

I am including a line of code for a three tier commission structure so you can see what I am talking about. The only difference between the two types of tiers is the extra tier (47%)

=IF(C7<=1000,C7*0.45,IF(AND(C7>1000,C7<=1500),(450+(C7-1000)*0.5),IF(AND(C7>1500,C7<10000),450+250+(C7-1500)*0.6,"oops")))

I am having trouble with the code for this stepped scale commission structure.

Net Service
$1,1400

Step Scale Comm. %
$0-500 40%
$500-750 45%
$750-1000 47%
$1000-9999.99 50%

Final Commission Paid $ ?