Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Graduated Commission Structure

Topic Labels: Formulas
Solved
Jump to Solution
1304 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Groupof7
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
Screenshot 2022-10-16 at 10.06.26 AM

Any help with this would be greatly appreciated.

Thank you

1 Solution

Accepted Solutions
augmented
10 - Mercury
10 - Mercury

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!

See Solution in Thread

2 Replies 2
augmented
10 - Mercury
10 - Mercury

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!