Calculating Tiered Commission with an IF statement

pixie222

New Member
Joined
Apr 3, 2009
Messages
6
Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
$0 - $15,999 – 1% commission<o:p></o:p>
$16,000 – $19,999 – 3.75% commission<o:p></o:p>
$20,000 - $24,000 – 4.5% commission<o:p></o:p>
$25,000 – unlimited – 5.5% commission<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
Would anyone know how to calculate this for me? Any help would be appreciated. <o:p></o:p>
Thanks!! <o:p></o:p>
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
If I'm understanding you correctly, the % is applied to the entire balance? Like this?
Excel Workbook
ABCDEF
1AMT%% x AMTLookup Table (used by row 2 formulas, not needed by row 3 formulas)
2159990.01159.9900.01
3160000.0375600160000.0375
4199990.0375749.9625200000.045
5200000.045900250000.055
6249990.0451124.955
7250000.0551375
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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

Well-known Member
Joined
Apr 16, 2008
Messages
578
=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?
 
Last edited:

pixie222

New Member
Joined
Apr 3, 2009
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,509
Messages
5,572,582
Members
412,472
Latest member
stgermainr
Top