Sliding Scale Commission

Hello,

I wonder if someone would help me with sliding scale commission, :-( I have created this spreadsheet that is to calculate commission (on Sliding Scale basis), however, I can not create a formula that will give me the exact commission rate between scales.. for example, between 0 - 50k our consultant will receive 10% commission and from 50K - 100K 15% commission - but if the consultant bills 57k I need my spreadsheet to calculate 10% on the 1st 50k and 15% on the 7k..

So basically this is way beyond me..I could easily do this on a calculator but would like a spreadsheet that tally's this all up for me..

Please please would someone help me, I am going out of my mind with IF's and SUMS and all other functions..

Attached is an example of my spreadsheet.

Thanks for your help

In Cell I10 paste the following formula
=CHOOSE(IF(E10<=$J$3,1,IF(E10<=$J$4,2,IF(E10<=$J$5,3,IF(E10<=$J$6,4,IF(E10<=$J$7,5,6))))),E10*$K$3,(E10-$J$3)*$K$4+5000,(E10-$J$4)*$K$5+12500,(E10-$J$5)*$K$6+32500,(E10-$J$6)*$K$7+62500,(E10-$J$7)*$K$8+97500)

Copy the formula down

<click on below 'star' if this helps>

Commission Sheet.xlsb

Bringing back an old request that matches, pretty close, to the same issue I am having. I doctored the attachment to match my needs but the math doesn't work. Can someone assist?

When I cross the threshold for the next rate, it needs to add the lower rate plus the higer rate. So in my example, the commission on 2k should be 284....875*.1247 and 1,125*.1559...

I think that too much effort has been put into trying to get the formula into one cell and you end up with the complicated formula above (and full marks to KM - the formula agrees with my figure.) (lexiswill - are you sure that you "doctored" correctly?)

By spreading it out a little, it becomes much clearer and more importantly auditable (checkable) which, in the case of commission, is very important.
The formula in L4 can be copied down as far as is necessary

Regards
Alastair

Seems like someone has w-a-a-y too much time on his hands


Regards
Alastair!

I'm sure those both work and get me to what I need but I am still having a hard time working it out. Can we use my numbers and plug in the formulas? What I need to see is the amount of $284.50 in the cell K8. Right now it isn't doing the full calculation. Any help would be greatly appreciated and thank you all for the assistance already.

I am using my spreadsheet, attached, and K8 is the final commission minus deductions (which are 0).

I am getting $284.50 from the below math...

In my example, the commission on the second sale of 2k (where $875 should be at the lower rate and $1,125 should be at the higher rate) is ... $875*.1247 = $109.11 and $1,125*.1559 = $175.39. Thats the total I need to get to in a formula so that the rep gets paid at both rates for this deal.

Does that help?

Hi lexiswill

I see where you are coming from. My solution was to work out the total commission for the period and you want the individual parts of the total. If you need this, then you will have to have a system where you calculate the unused balance of each commission band. With the two bands in the example this is pretty easy - it gets a bit more complicated once you have lots of bands and even more complicated when you add lots of payments.

Regards
Alastair