# Calculating Tiered Commission with an IF statement

#### pixie222

##### New Member
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>

### 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
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
=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
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

Replies
3
Views
604
Replies
3
Views
291
Replies
7
Views
996
Replies
2
Views
369
Replies
10
Views
2K