Need Formula Help

[AllThings]

We need a formula to help us figure commissions. The structure is:

.01 - 1,200.99 is paid at 41%
1,201 - 2000.99 is paid at 43%
2001 - 3000.99 is paid at 47%
3001 & up is paid at 50%

How do I get it to figure each piece in one formula? Example

For someone with 5000 commission-able sales the breakdown would be:

up to 1200 - 492
1201 - 2000 - 343.57
2001 - 3000 - 469.53
3001 - 5000 - 999.50
Total of - 2,304.60

Thanks!

 

[sheetspread]

Excel 2010
	A	B
1	5000	2304.6
Sheet1

Cell Formulas
Range	Formula
B1	=MIN(A1,1200)*0.41+MEDIAN(0,A1-1201,799)*0.43+MEDIAN(0,A1-2001,999)*0.47+MAX(A1-3001,0)*0.5

(you can also write a lookup or sumproduct formula for a separate table)

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	Amount	5,000.00			Bracket	Rate	Rate_Diff
2	Commission	2,306.00			0.00	41%	41%
3					1,200.00	43%	2%
4					2,000.00	47%	4%
5					3,000.00	50%	3%
6
7		2,306.00
8
9b

Cell Formulas
Range	Formula
B2	= SUMPRODUCT(--(B1>rB),B1-rB,rDiff)
B7	=MIN(B1,1200)*0.41+MEDIAN(0,B1-1200,800)*0.43+MEDIAN(0,B1-2000,1000)*0.47+MAX(B1-3000,0)*0.5
G2	=F2-N(F1)

Named Ranges
Name	Refers To	Cells
rB	='9b'!$E$2:$E$5
rDiff	='9b'!$G$2:$G$5