Need a tier based commission calculating formula

[Jmatt856]

For a company, a salesperson gets paid the following way entirely in commission:

11% on all sales up to $100,000
12% on all sales $100,001 - 175,000
13% on all sales $175,001 - 250,000
14% on all sales $250,001 - 375,000
15% on all sales $375,001+

 

[PCL]

if your data stands in columns ( A for amount, B for commision ) see the use of VlookUp

 

[Joe4]

Here is one way:

=(MIN(A1,100000)*11%)
+(MAX((MIN(A1,175000)-100000),0)*12%)
+(MAX((MIN(A1,250000)-175000),0)*13%)
+(MAX((MIN(A1,375000)-250000),0)*14%)
+(MAX(A1-375000,0)*15%)

(I put it on multiple lines just to make easier to read and follow along)

 

[MARK858]

Slightly different way (I could put it all in one formula but easier to adapt in the future with a table)

Book1
	A	B	C	D	E	F
1	Threshold	Margin	Margin diff		Sales	Comm
2	0	11%	11%		190000	21950
3	100000	12%	1%
4	175000	13%	1%
5	250000	14%	1%
6	375000	15%	1%
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(E2>$A$2:$A$6),(E2-$A$2:$A$6), $C$2:$C$6)
C2	=B2
C3	=B3-B2
C4	=B4-B3
C5	=B5-B4
C6	=B6-B5