Determining Rates Based on Specific Ranges

[Grizzly0610]

Hello I have some experience in excel but not much. My boss at works wants me to make an excel worksheet that we can send out to our clients to determine the amount they will owe us on any job that comes up. Pending on the job amount the client their amount will vary. For example if they were to get a job for 500,000 we will charge 2.5% for the first on 200,000 and 2% for the next 300,000. How can i make a worksheet where one cell will give me the amount they would owe us. Of course we are dealing with more complicated numbers but something generic where I can insert the percentages and amount based on the different clients we have would be much appreciated. I will try to make an example of something i would deal with below.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]650,000
[/TD]
[/TR]
[TR]
[TD]First
[/TD]
[TD]100,000
[/TD]
[TD]2.5%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Next
[/TD]
[TD]250,000
[/TD]
[TD]2%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Next
[/TD]
[TD]300,000
[/TD]
[TD]1.5%
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

have a read here and see if this helps at all
methods to create a tired commission table and use
sumproduct or vlookup
https://www.excelcampus.com/modeling/calculate-commissions-with-tiered-rate-structure/

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	Amounts	Commissions
2	650,000.00	12,000.00		Bracket	Rate	Rate_Diff
3	200,000.00	4,500.00		0	2.50%	2.50%
4	10,000.00	250.00		100,000	2.00%	-0.50%
5	1,000,000.00	17,250.00		350,000	1.50%	-0.50%
6				650,000
7
2b

Cell Formulas
Range	Formula
B2	= SUMPRODUCT(--(A2>$D$3:$D$6),A2-$D$3:$D$6,$F$3:$F$6)
F3	=E3-N(E2)