Function or VBA for bonus scale

tsitsicat1

New Member
Joined
Jun 27, 2018
Messages
10
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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:
Upvote 0
try

ABCDEF
AmountBonus

<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!!!
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,698
Members
453,747
Latest member
tylerhyatt04

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top