IF formula for BONUS system

[pavlos1975]

I am seeking your assistance on a formula for a bonus system. The target minimum sales is 180,000 and I would like to calculate the bonus as follows:

under 180,000 = zero bonus
180,000 to 210,000 = 2% bonus of the sales
210,000 and above = 4% bonus of sales
Therefore, if for example 250,000 is achieved, there is zero bonus for the amount under 180,000; 2% bonus on the 30,000 in between 180,000 and 210,000 and 4% on the 40,000 above 210,000 which will equal:

2% of 30,000 = 600
4% of 50,000 = 2000

Total bonus = 2600

I have tried to use some of the IF equations on this site, but cannot seem to work it out.

I am not so experienced on excel. How can I put this in a formula format. so assuming the base (180k) is in cell A1 and the the 2% is in cell A2; the 4% us in B1

[FDibbins]

Hi and welcome to the forum

I think your calc is a little off, based on your sample, you have 40000 above 210 000, not 50 000. Try this...
=if(a1<180000,0,if(a1<210000,(a1-180000)*2%,(a1-210000)*.04+600)

[shg]

Another way:

	B	C	D	E	F
4	Sales	Comm Rate	Delta
5	$0	0.0%	0.0%		D5 and down: =C5-N(C4)
6	$180,000	2.0%	2.0%
7	$210,000	4.0%	2.0%
8
9	Sales	Commission	Eff Rate
10	$180,000	$0	0.0%		C10 and down: =SUMPRODUCT((B10>$B$5:$B$8) * (B10 - $B$5:$B$8) * $D$5:$D$8)
11	$190,000	$200	0.1%		D10 and down: =C10/B10
12	$200,000	$400	0.2%
13	$210,000	$600	0.3%
14	$220,000	$1,000	0.5%
15	$230,000	$1,400	0.6%
16	$240,000	$1,800	0.8%
17	$250,000	$2,200	0.9%
18	$260,000	$2,600	1.0%
19	$270,000	$3,000	1.1%
20	$280,000	$3,400	1.2%
21	$290,000	$3,800	1.3%
22	$300,000	$4,200	1.4%

[pavlos1975]

Hi FDibbins. Thanks for your prompt reply and help. Its almost there I actually would like the cells to be calculated automatically. So if I change the sales, the amount is calculated without entering the 600. If that makes sense. please see attachment...... 1213.jpg

[AlKey]

Hi Pavlos,

Please see attached file with bonus calculations. Formula provided earlier by FDibbins.

[pavlos1975]

Dear AlKey. You're the best..... Thank you, thank you, thank you ))))

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation *

[FDibbins]

I know this is solved, but I just wanted to comment on this....

So if I change the sales, the amount is calculated without entering the 600. If that makes sense.

The 600 is what would have been calc'd anyway if the amount was over 210 000, it just saved adding in an extra calc

Thanks for the feedback