# 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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.

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!

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.

 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:
I would like to see the expected results, too (not just the data).

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%

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)

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
9
Views
254
Replies
0
Views
308
Replies
10
Views
465
Replies
3
Views
194
Replies
3
Views
276

1,203,663
Messages
6,056,623
Members
444,878
Latest member
SoupLaura

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