Tiered commissions calculator

[antsal]

I am trying to create a formula for a tiered commissions calculator and have come up with the following:

=IF(C7<1000001,C7*0.02, IF(C7>1000001,C7*0.035,IF(C7>3000001,C7*0.04, IF(C7>5000001,C7*0.05))))

It doesn't seem to work properly, the calculation stops after the IF(C7>1000001,C7*0.035 IF STATEMENT and does not go on to use the rest of the formula.
Any assistance fixing this or creating a tiered commission calculation based on the 2% 3.5% 4% & 5% against the figures above would be great.

Thanks.

 

[Aladin Akyurek]

Will try the VLOOKUP and see if that works.

Thanks so much for your help.

0	0.02
1,000,000	0.035
3,000,000	0.04
5,000,000	0.05
700,000	14,000	14,000	14,000	14,000	14,000
4,000,000	140,000	160,000	160,000	160,000	160,000
8,000,000	280,000	400,000	400,000	400,000	400,000

<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY>
</TBODY>

C2:D4 houses tiers and rates. If so desired, C1:C4 --> TIERS and D1:D4 --> RATES.

D7, copied down:

=LOOKUP(C7,TIERS,RATES)*C7

E7, copied down:

=LOOKUP(C7,$C$1:$C$4,$D$1:$D$4)*C7

F7, copied down:

=LOOKUP(C7,{0;1000000;3000000;5000000},{0.02;0.035;0.04;0.05})*C7

G7, copied down:

=VLOOKUP(C7,$C$1:$D$4,2,1)*C7

H7, copied down:

=INDEX($D$1:$D$4,MATCH(C7,$C$1:$C$4,1))*C7