Formula to work out commission structure

[ronie85]

Hi, I require a formula to work out a sales commission structure as a lot of online templates seem to be different structures.

In a nutshell, when looking at the picture, I will require 5 of the same formulas which I can replicate which need to go into the cells in yellow in G2, G3, G4, G5 & G6.
For this basis I will just need the formula for G2 and I can amend it.

The formula will need to take the source value within A2 each time. It will need to tell me the amount of that formula that falls between the lower value C2, and higher value D2. In this case £200,000.00. The same formula then needs to return the percentage of this overall value as shown in E2. As it is 0% it returns £0.
However as you can see within G3 it has returned £1,875.00 which is 1.25% of £149,999.99 (the value below £400,000 and above £250,000.01)

For reference, all values shown within the yellow cells I have inputted myself until I get the formula.

Thanks in advance.

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E	F
1	Sales		Min	Max	Rate	Commission
2	900,000.00		0.00	250,000.00	0.00%	0
3			250,000.01	400,000.00	1.25%	1,875.00
4			400,000.01	575,000.00	1.55%	2,712.50
5			575,000.01	800,000.00	1.75%	3,937.50
6			800,000.01	9,999,999.00	2.00%	2,000.00
7
8						10,525.00
9
Sheet1

Cell Formulas
Range	Formula
F2	F2	=MAX(0,MIN(D2,$A$2)*E2)
F3:F6	F3	=MAX(0,MIN(D3-D2,$A$2-D2)*E3)
F8	F8	=SUM(F2:F6)

 

[Eric W]

Consider:

Book1
	A	B	C	D	E	F	G	H
1	Sales to Date		Tier Minimum	Tier Maximum	Payout Rate	Delta	Commission
2	900000		0.00	250000.00	0.00%	0.00%	0
3			250000.01	400000.00	1.25%	1.25%	1875
4			400000.01	575000.00	1.55%	0.30%	2712.5
5			575000.01	800000.00	1.75%	0.20%	3937.5
6			800000.01	9999999.00	2.00%	0.25%	2000
7
8							10525	10525
Sheet3

Cell Formulas
Range	Formula
F2:F6	F2	=E2-N(E1)
G2:G6	G2	=MEDIAN(0,D2-C2,A$2-C2)*E2
G8	G8	=SUM(G2:G6)
H8	H8	=SUMPRODUCT((A2-C2:C6),F2:F6,--(A2>=C2:C6))

The formulas in column G are essentially the same as what AhoyNC provided. An alternative is to add the formulas in column F. They are just the differences in rates between the tiers. Then you can use the single cell formula in H8 to get your result.