I’ve done a couple of similar examples but here is one question I got a few days ago:
I am trying to write a formula to pay a percentage up to a certain amount and then increase the percentage once a rep exceeds that amount. Ex: Rep brings in $2,000.
Pay 50% for the first $600
Pay 60% for the next $600
Pay 70% for the next $600
Pay 80% for everything over $1,800.
How will I get it done? Let’s start from the beginning. If my sales number is in cell C6, then I’d start off by checking if the sales is above $1800. If it is, then I’d simply add:
50% of $600+60% of $600 + 70% of $600 to the 80% of everything over $1800. In short it would be:
=IF(C6>=F9,1080+(C6-F9)*H9,1)
As you can see, it works well for that kind of number:
But would not work for a number under $1800:
So I will then work my way to that category:
=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),2))
Of course, I must also adjust for numbers in the second category, I will do so with the following:
=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),IF(C6>F7,I6+H7*(C6-F7),3)))
And finally, I can replace the “default” here. If the number is not in category 2, we can assume that it is in category 1 (less than $600 in this case) so I simply replace 3 by:
=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),IF(C6>F7,I6+H7*(C6-F7),C6*H6)))
And here you go, it works and easy to adjust. You can modify the percentages, levels, etc.
You can download the spreadsheet here!
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************