Function or VBA for bonus scale

[tsitsicat1]

Hi there,

I need to calculate the bonus to be received by an employee who has generated total sales of for example $180.000. After all the calculations he must take $6.800.
The bonus scale is
8% for amounts between 120001-130000,
plus 10% for amounts between 130001-140000
plus 11% for amounts between 140001-150000
plus 12% for amounts between 150001-160000
plus 13% for amounts between 160001-170000
plus 14% for amounts between 170001-180000

HELP!!

Thanks to anyone who's able to help in advance.

 

[AlanY]

try

Book1
	A	B	C	D	E	F
1	Amount		Commission		Amount	Bonus
2	0	0%	0%		180,000	6,800
3	120,001	8%	8%
4	130,001	10%	2%
5	140,001	11%	1%
6	150,001	12%	1%
7	160,001	13%	1%
8	170,001	14%	1%
167

Cell Formulas
Range	Formula
F2	=SUMPRODUCT((E2 > A2:A8) * (E2 - A2:A8) * C2:C8)
C3	=B3-B2

 

[tsitsicat1]

try

	A	B	C	D	E	F
Amount	Bonus

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Commission[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 180,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"] 6,800[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"] 120,001[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"] 130,001[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"] 140,001[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"] 150,001[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"] 160,001[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"] 170,001[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

167​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMPRODUCT((E2 > A2:A8) * (E2 - A2:A8) * C2:C8)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=B3-B2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You are awesome!!! Thank you so much!!!

 

[AlanY]

you're welcome

 

[Dave Patton]

Attention: Shamsu

Check how you specify the brackets.
The example below shows a small difference in the total calculation.

Excel 2010
	A	B	C	D	E	F
1	Income	Tax		Brackets	Rate	rDiff
2	1,500,000.00	260,000.00		0.00	0%	0%
3	354,000.00	2,700.00		300,000.00	5%	5%
4				500,000.00	20%	15%
5				1,000,000.00	30%	10%
6
7	Manual calculation
8	0.00	to	300,000.00	0%	0.00
9	300,000.00		500,000.00	5%	10,000.00
10	500,000.00		1,000,000.00	20%	100,000.00
11	1,000,000.00		1,500,000.00	30%	150,000.00
12					260,000.00
13
4a

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>rB),A2-rB,rDiff)
B3	=SUMPRODUCT(--(A3>rB),A3-rB,rDiff)

Named Ranges
Name	Refers To	Cells
rB	='4a'!$D$2:$D$5
rDiff	='4a'!$F$2:$F$5

 

[Dave Patton]

Sorry, I posted this to the wrong thread.