Trying to create a formula to calculate commission in a month.
If I make a company £18,000 in a month commission would be £2599.65.
Commission banding is
0-5,000 – 10%
5001-15,000 – 15%
15,001 or more – 20%
How would you create a formula to calculate the commission due?
—–
Alex
Create a table to hold the commission rates. So the first column in this "Commission" table would be the amount, the second column would be the rate. In the first row you'd have 0 and 0.1 (10%). In the second row would be 5001 and 0.15 and the third row would be 15001 and 0.2.
In when you want to calculate a commission amount in the main table, just use LOOKUP to find the rate.
For instance, if B3 held the amount 6000, then the formula in B4 could be LOOKUP(B3,Commission::A,Commission::B). This would look up 6000 in the table and return 0.15 since it is greater than or equal to 5001 but less than 15001.
Hi, thanks for the quick reply.
In my example £18,000 means £2599.65 commission because you get the % of each bracket added together, not % of the total amount of profit generated. Example below.
So you don’t get 20% of £18000, you get 10% of first 5k, 15% of next 999 etc. Example below
5000 - 0 = £5000* 0.1 = £500
15000 - 5001 = £999*0.15 = 1499.85
18000 - 15001 = £2999*0.2 = £599.8
Therefore 500+1499.85+599.8 = £2599.65
Is there a way to create a formula like that?
Thanks!
Alex: That's a different story. I don't have an elegant solution for that, so I hope you don't have too many of these tiers. You would just use subtraction and the MIN or MAX functions to calculate it just like you show in your example.
MIN(B3,5000)*0.1 + MAX(MIN(B3-5000,0),10000)*0.15 + MAX(B3-15000,0)*0.2
So the first MIN cuts the amount off at 5000 if it is higher. The second part subtracts 5000 from B3 but the MIN doesn't allow a number less than 0. Then the MAX doesn't allow a number greater than 10000. Then the last one is a MAX that looks for the amount over 15000, but doesn't produce any number less than 0.
If you have more tiers, they would look like the middle part, with both an outer MAX and inner MIN.
Gary, I recently worked on a similar problem and wanted to share in case Alex might find it useful. The solution uses nested if statements and although I set this to work up to 18000 in accordance with Alex's example tiers it could easily be modified to use different commission tiers. IF(B3<5001,B3×0.1,IF(B3<15001,500+((B3−5001)×0.15),IF(B3<18001,1999.85+((B3−15001)×0.2),"OUT OF RANGE"))) My testing seemed to provide results in accordance with Alex's example. Not elegant but seems to work.