Forum Discussion
Tiered Commission Rate After Cumulative Sales Volume is Reached
Since you've gone to the trouble of creating a spreadsheet with dummy names in order to post an image of it here, would you be so kind as to put a copy of that spreadsheet on OneDrive or GoogleDrive and then post a link to it here, Otherwise, in order for us to write a formula and test it in your context, you're asking that we create the whole spreadsheet.......which you already have done. Help us help you, by posting a copy as noted.
I notice that you have a table on the second sheet, a table for different classifications of "Agent" vs "Associate," with the former receiving slightly higher (5%) percentages for the same cutoffs.
It brought to mind our Federal Income Tax Tables. And I wonder if you could use a variation on the approach I've created here in the attached for calculating taxes based on different filing statuses.
In the meantime, while you play with that, although your example is valid for a simple formula for "John Doe" -- I wonder if you wouldn't be better served if you could give us a bit more guidance on how you plan to use this for the entire agency. Do you have a single sheet that records all sales and whether the person involved is a broker or associate? Do you intend to do this calculation based on each sale, paying the commission due then and there, or is it more of a monthly thing, where multiple sales during a month get accounted for in total? Or quarterly?
Would it help, in short, to create a "dashboard" sheet that pulled data for a given agent for a given period (as well as all periods that preceded it in the year) and gave a single "commission earned for the month" or "...quarter" or, possibly, "...for the year?
I ask because there are so many different ways to look at this, and your sample sheet carries the heading "Closing Record 2022" implying that it's meant to be a year-end calculation. In which case, one could do one calculation very easily on ALL the sales due to John Doe, rather than a line by line calculation as you're currently trying to do.
(These are all questions I'd be asking if we were sitting face-to-face, working to design a spreadsheet that not only gives you the correct calculations but also does so in ways that comport with the actual way you'll be using it.)
- Our broker receives a 5% higher commission split than other agents. I do have dashboards with sumif formulas to show how much volume and commission is earned each month. The sheet will show every closing from every agent throughout the entire year. The sheet says 2022 but it will be used as a template for 2023
