Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Graduated Commission Structure

Topic Labels: Formulas
Solved
Jump to Solution
152 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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!