# Formula for Commission

#### jlr123

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

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

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

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

 If Category Over Monthly Goal less Monthly goal All Rental All Onsite All EQS All Test Bench All Lab Svcs Total Monthly Base Tier 1 Tier 2 Tier 3 Total 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 - Base Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-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 - Electrical Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Electrical 16,684.00

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

Last edited:

#### Joe4

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

#### jlr123

##### New Member

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

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

Replies
3
Views
153
Replies
5
Views
339
Replies
4
Views
231
Replies
11
Views
711
Replies
1
Views
329 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

1,163,713
Messages
5,833,269
Members
430,200
Latest member ### 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