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
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%

<tbody>
</tbody>

167​

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

<tbody>
</tbody>

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.