I have an issue that I've been able to solve using an overly complicated series of functions, but it's an error-trap and I have a feeling it can be accomplished much more simply, and I'm just not up to the task...
Company X has a sales goal for a 4 year period. It pays commission percentages as that goal is achieved, but they are tiered percentages: one percentage is applied to the first 25% of the goal number, another is applied to the second quarter, and so on. Using 100,000 as a hypothetical sales goal, it would look like this:
5% on dollars 1 through 25,000
6% on dollars 25,001 through 50,000
7% on dollars 50,001 through 75,000
8% on everything after 75
I then have projected sales, or progress against that goal, broken down by years:
Year 1: $20,000 in sales
Year 2: $35,000 in sales
Year 3: $30,000 in sales
Year 4: $25,000 in sales
My goal is to set up a formula that will allow me to place an annual sales gross next to a year, and have the next cell calculate how much would be paid in commission that year. Year 1 we would be under 25,000, so it would be a simple 5% of 20,000 - but in year 2, I'd have $5,000 at 5%, 25,000 at 6%, and $5,000 at 7%. Easy to do by hand, but looking for an elegant solution via xls.
I know I'll need a reference table of some type for the goal and the percentages, but can't seem to land on anything that isn't overly complex.
Thanks in advance,
Bookmarks