Formula for Commission

jlr123

New Member
Joined
Mar 6, 2014
Messages
31
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!!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,483
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
31
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
54,483
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
31

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
54,483
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
31

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
54,483
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
31
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,201
Messages
5,527,386
Members
409,759
Latest member
KCH

This Week's Hot Topics

Top