I am tring to write a formual to calucate commission for sales of an item. I started with an IF statement and found that i have more than 7 statements which makes the IF statement unusable.
Here is the deal.
1) for up to 250K the commission is 10%
2) on the next portion up to 500K is 7%
3) on the next part upto 1000K is 5%
4) on the next part upto 2000K is 3%
5) on the rest over 2000K is 2%
Example:
If the sales price = 1200K
1) = 25K (250K*10%)
2)= 17.5K (500-250K = 250K * 7%)
3) = 25 K (1000K -500K = 500K * 5%)
4) = 6K (1200k - 1000K = 200 K *3%)
= 73.5K
Just in case K=1,000