Formula for Commission

jlr123

New Member
Joined
Mar 6, 2014
Messages
40
I need the formula for the following:

If B44 = 0 -20000 then 0%
If B44 =20001-30000 then B44-20000 * 2%
If B44 =30001-40000 then B44-20000*3%
If B44=40001-50000 then B44-20000*4%

Another issue is that this monthly, so each month the Cell moves to the next column (i.e. Feb cell would be C44). However I can change it manually each month.It is just a pain.

Appreciate your help!!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,289
Office Version
  1. 365
Platform
  1. Windows
Here is one way:
Code:
=IF(B44>40000,(B44-20000)*.04,IF(B44>30000,(B44-20000)*.03,IF(B44>20000,(B44-20000)*.02,0)))

But one question. What if B44 is greater than 50000? What should happen then?
If they do not get any bonus above 50000, then amend the formula above to:
Code:
=IF(B44>40000,MIN((B44-20000),30000)*.04,IF(B44>30000,(B44-20000)*.03,IF(B44>20000,(B44-20000)*.02,0)))

Another issue is that this monthly, so each month the Cell moves to the next column (i.e. Feb cell would be C44). However I can change it manually each month.It is just a pain.
If you copy the formula over to the next column, the column references in the formula should adjust automatically by one column.
 

jlr123

New Member
Joined
Mar 6, 2014
Messages
40
Here is one way:
Code:
=IF(B44>40000,(B44-20000)*.04,IF(B44>30000,(B44-20000)*.03,IF(B44>20000,(B44-20000)*.02,0)))

But one question. What if B44 is greater than 50000? What should happen then?
If they do not get any bonus above 50000, then amend the formula above to:
Code:
=IF(B44>40000,MIN((B44-20000),30000)*.04,IF(B44>30000,(B44-20000)*.03,IF(B44>20000,(B44-20000)*.02,0)))


If you copy the formula over to the next column, the column references in the formula should adjust automatically by one column.

This is great but what if I have three cells, to show what Percentage the person is getting and each cell would result in $0 or whatever the amount would be.

Tier 1 Tier 2 Tier 3
2% 3% 4%
=Amt =Amt =Amt

Thanks so much!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,289
Office Version
  1. 365
Platform
  1. Windows
This is great but what if I have three cells, to show what Percentage the person is getting and each cell would result in $0 or whatever the amount would be.
Please post a specific example (with real numbers), so I can see exactly what you are looking for.
 

jlr123

New Member
Joined
Mar 6, 2014
Messages
40

ADVERTISEMENT

If Category Over Monthly Goal less Monthly goal
All RentalAll OnsiteAll EQSAll Test BenchAll Lab SvcsTotal Monthly BaseTier 1Tier 2Tier 3Total Incentive
2.50%2.50%0.50%1.50%2.00%1.00%2.00%3.00%
471.85 927.41 194.39 - 830.91 2,424.56 - -
THIS YEAR
Revenue Type - BaseJan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19
Rental 18,873.98
Onsite 37,096.49
Equip Sales 38,877.18
All Test Bench -
Lab Service 41,545.70
Total Base Revenue 136,393.35 - - - - - - - - -
THIS YEAR
Revenue Type - ElectricalJan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19
Electrical 16,684.00

<tbody>
</tbody>
The Electrical cell is what is to be calculated in the Tiers (16,684)
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,289
Office Version
  1. 365
Platform
  1. Windows
I would like to see the expected results, too (not just the data).
 

jlr123

New Member
Joined
Mar 6, 2014
Messages
40

ADVERTISEMENT

I would like to see the expected results, too (not just the data).

The result in each tier cell would be according to this table: B44 represents the 16,684. In each cell according to the data in the table the 16,684 would result in 0%. However is cell reflected 25,000 then Tier 1 cell would be 25000-20000=5000*1%. Total calculation would end as ($50.00)

If B44 = 0 -20000 then 0%
If B44 =20001-30000 then B44-20000 * 1%
If B44 =30001-40000 then B44-20000*2%
If B44=40001-50000 then B44-20000*3%
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,289
Office Version
  1. 365
Platform
  1. Windows
OK, it looks like you changed the percentages too:

Original post:
If B44 = 0 -20000 then 0%
If B44 =20001-30000 then B44-20000 * 2%
If B44 =30001-40000 then B44-20000*3%
If B44=40001-50000 then B44-20000*4%

New post:
If B44 = 0 -20000 then 0%
If B44 =20001-30000 then B44-20000 * 1%
If B44 =30001-40000 then B44-20000*2%
If B44=40001-50000 then B44-20000*3%

Based on the new post, here are each Tier's formulas:
Tier 1: =IF(AND(B44>20000,B44<=30000),(B44-20000)*0.01,0)
Tier 2: =IF(AND(B44>30000,B44<=40000),(B44-20000)*0.02,0)
Tier 3: =IF(AND(B44>40000,B44<=50000),(B44-20000)*0.03,0)
 

jlr123

New Member
Joined
Mar 6, 2014
Messages
40
OK, it looks like you changed the percentages too:

Original post:


New post:


Based on the new post, here are each Tier's formulas:
Tier 1: =IF(AND(B44>20000,B44<=30000),(B44-20000)*0.01,0)
Tier 2: =IF(AND(B44>30000,B44<=40000),(B44-20000)*0.02,0)
Tier 3: =IF(AND(B44>40000,B44<=50000),(B44-20000)*0.03,0)

You're the best.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,713
Messages
5,833,269
Members
430,200
Latest member
ADLHMA2022

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
Top