# 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

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

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
1
Views
163
Replies
3
Views
330
Replies
0
Views
22
Replies
3
Views
270
Replies
1
Views
65