How to look up a payrate?
I have two tables set up, Payrates & a Sales Log.
I am paid on a sliding commission scale, which based on profit margin and looks up the appropiate Payout Rate…
For example, If I sold a job with a profit margin between 0.47 and 0.4899, it would return a pay rate of 11.5% from the Sliding Payrate Scale
On this example, I have a job with a profit margin of 0.50% and should pay a 12.5% commission. What is the best way to set this up? Included a screenshot to help clarify what I’m trying to accomplish.
Unfortunately, @William_B, you’re going to have to set your “Payrates” table up horizontally instead of vertically.
Think of each Record (row) in the “Payrates” table as a “Pay Schedule”. Initially, you only need one, your current pay schedule/rates. You’ll need to create a Field (column) for each “Tier” that could be matched against, and it’s percentage value as the Value for that “Pay Schedule”. The advantage of this is that you can change your pay structure in the future by adding a new “Pay Schedule” record, and using that new record on all new sales items going forward – this will preserve your historical data if you change your payout rates.
So in your “Sales Log”, you’ll have to link each sale to a Pay Schedule record from the Payrates table. Then, tediously, you will have to pull in each value from your Pay Schedule with Lookups. Lastly, you can now create a nested “IF()” formula to find the appropriate payout rate for the sale, based on the percentage.
Here’s a simple demo base you are welcome to join and poke around in to get the idea:
Airtable: Organize anything you can imagine
Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.
I’ve used this same type of structure for “Fee Schedules” before too – businesses that sell goods or services, and need the flexibility in their system to be able to change their pricing structure. This is the best way to achieve that in a way that does not alter historical data when schedules are changed.
Let me know if you need further help.
