Graduated Commission Structure

[Groupof7]

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

[augmented · Accepted solution]

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!

[Groupof7]

Thank you so much for your help!