Skip to main content

I am looking for some help calculating a graduated sales commission in Airtable.


The sales commission is calculated at a rate of twenty-five percent (25%) on the part of the sale price up to and including $25,000; plus twenty percent (20%) on the part of the sale price over $25,000 and up to and including $100,000; plus fifteen percent (15%) on the part of the sale price over $100,000.


If the sale price is $150,000, then the commission is 25% of the first $25,000, then 20% on the amount from $25,001-$100,000, then 15% on the amount from $100,001-$150,000.


If the sale price is $5,000, then the commission is 25% of $5,000.


The commission structure is visualized like this:


Any help with this would be greatly appreciated.


Thank you

Hi. I used your table to give you a sample answer. I think it should work…


IF({sales price},

  (MIN({sales price},25000)*0.25)+

  (MIN((100000-25000),MAX(0,{sales price}-25000)))*0.2+

  (MIN((200000-100000),MAX(0,{sales price}-100000)))*0.15

)

Of course you can simplify the first argument to “MIN” in the 2nd and 3rd cases, but I just wanted to show you how to incorporate your commission levels if they change in the future.


No guarantees that it’s mistake free. Ha!

Hi. I used your table to give you a sample answer. I think it should work…


IF({sales price},

  (MIN({sales price},25000)*0.25)+

  (MIN((100000-25000),MAX(0,{sales price}-25000)))*0.2+

  (MIN((200000-100000),MAX(0,{sales price}-100000)))*0.15

)

Of course you can simplify the first argument to “MIN” in the 2nd and 3rd cases, but I just wanted to show you how to incorporate your commission levels if they change in the future.


No guarantees that it’s mistake free. Ha!


Thank you so much for your help!

Reply


Terms & Conditions