Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 16, 2022 07:07 AM
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
Solved! Go to Solution.
Oct 16, 2022 08:16 AM
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!
Oct 16, 2022 08:16 AM
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!
Oct 16, 2022 08:42 AM
Thank you so much for your help!