Calculating Tiered Commission with an IF statement

[pixie222]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I need a formula to help calculate the commission for my team at work. We are collectors and take credit card payments to clear up delinquency. Here is how the tiers work:<o></o>
<o> </o>
$0 - $15,999 – 1% commission<o></o>
$16,000 – $19,999 – 3.75% commission<o></o>
$20,000 - $24,000 – 4.5% commission<o></o>
$25,000 – unlimited – 5.5% commission<o></o>
<o> </o>
The part I am having troubles with is, if you were to collect $35,000 – you would qualify for 5.5% on the whole balance. If you collect $22,000 – you would qualify for 4.5% on the whole balance. <o></o>
<o> </o>
Would anyone know how to calculate this for me? Any help would be appreciated. <o></o>
Thanks!! <o></o>

 

[taigovinda]

If I'm understanding you correctly, the % is applied to the entire balance? Like this?

Excel Workbook
	A	B	C	D	E	F
1	AMT	%	% x AMT		Lookup Table (used by row 2 formulas, not needed by row 3 formulas)
2	15999	0.01	159.99		0	0.01
3	16000	0.0375	600		16000	0.0375
4	19999	0.0375	749.9625		20000	0.045
5	20000	0.045	900		25000	0.055
6	24999	0.045	1124.955
7	25000	0.055	1375
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,$E$2:$E$5,$F$2:$F$5)
B3	=LOOKUP(A3,{0,16000,20000,25000},{0.01,0.0375,0.045,0.055})
C2	=LOOKUP(A2,$E$2:$E$5,$F$2:$F$5)*A2
C3	=LOOKUP(A3,{0,16000,20000,25000},{0.01,0.0375,0.045,0.055})*A3

 

[id107]

=IF(A1<15999,A1*0.01,IF(AND(A1>16000,A1<19999),A1*0.375,IF(AND(A1>20000,A1<24000),A1*0.45,IF(A1>25000,A1*0.55))))

should work.

Edit: Just noticed. There's a gap between 24,000 and 25,000. What should that come under?

 

[pixie222]

Hi id107,

Thank you SO much!!! With a quick modification this works perfectly.

the 24000 should have been 24999, sorry about that. But i have my spread sheet up to date and everything is working.

I can always count on this website.

Have a good day and thanks for your help!

~Pixie