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

Topic Labels: Formulas
Solved
152 2
cancel
Showing results for
Did you mean:
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:

Any help with this would be greatly appreciated.

Thank you

1 Solution

Accepted Solutions
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!

2 Replies 2
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!

5 - Automation Enthusiast

Thank you so much for your help!