# Function or VBA for bonus scale

#### tsitsicat1

##### New Member
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.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
try

Book1
ABCDEF
1AmountCommissionAmountBonus
200%0%180,0006,800
3120,0018%8%
4130,00110%2%
5140,00111%1%
6150,00112%1%
7160,00113%1%
8170,00114%1%
167
Cell Formulas
RangeFormula
F2=SUMPRODUCT((E2 > A2:A8) * (E2 - A2:A8) * C2:C8)
C3=B3-B2

Last edited:
try

ABCDEF
1AmountCommissionAmountBonus
200%0% 180,000 6,800
3 120,0018%8%
4 130,00110%2%
5 140,00111%1%
6 150,00112%1%
7 160,00113%1%
8 170,00114%1%

<tbody>
</tbody>
167

Worksheet Formulas
CellFormula
F2=SUMPRODUCT((E2 > A2:A8) * (E2 - A2:A8) * C2:C8)
C3=B3-B2

<tbody>
</tbody>

<tbody>
</tbody>
You are awesome!!! Thank you so much!!!

you're welcome

Attention: Shamsu

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

Excel 2010
ABCDEF
1IncomeTaxBracketsRaterDiff
21,500,000.00260,000.000.000%0%
3354,000.002,700.00300,000.005%5%
4500,000.0020%15%
51,000,000.0030%10%
6
7Manual calculation
80.00to300,000.000%0.00
9300,000.00500,000.005%10,000.00
10500,000.001,000,000.0020%100,000.00
111,000,000.001,500,000.0030%150,000.00
12260,000.00
13
4a
Cell Formulas
RangeFormula
B2=SUMPRODUCT(--(A2>rB),A2-rB,rDiff)
B3=SUMPRODUCT(--(A3>rB),A3-rB,rDiff)
Named Ranges
NameRefers ToCells
rB='4a'!\$D\$2:\$D\$5
rDiff='4a'!\$F\$2:\$F\$5

Last edited:
Sorry, I posted this to the wrong thread.

Replies
8
Views
355

1,203,236
Messages
6,054,303
Members
444,715
Latest member
GlitchHawk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back