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.

 

[Marcelo Branco]

Maybe this can help

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

M.

 

[antsal]

example of calculation:

C7= 5,000,000 therefore the calculation should be related to IF(C7>3000001,C7*0.04 statement and equal 200,000 instead the calculation comes out as 175,000 (or 3.5%) as per the previous IF STATEMENT.

The formula is designed to work out the commission on sales as a tiered structure ie.:

Up to 1000000 is 2%
1000001 - 3000000 is 3.5%
3000001 - 5000000 is 4%
>5000001 is 5%

As you will see any number higher than 1000001 is being calculated at the rate applicable to that not the rate for the higher tier.

 

[Aladin Akyurek]

example of calculation:

C7= 5,000,000 therefore the calculation should be related to IF(C7>3000001,C7*0.04 statement and equal 200,000 instead the calculation comes out as 175,000 (or 3.5%) as per the previous IF STATEMENT.

The formula is designed to work out the commission on sales as a tiered structure ie.:

Up to 1000000 is 2%
1000001 - 3000000 is 3.5%
3000001 - 5000000 is 4%
>5000001 is 5%

As you will see any number higher than 1000001 is being calculated at the rate applicable to that not the rate for the higher tier.

Instead of explaining of how your If works or should work, try to state the commissions for the following values:

700,000

4,000,000

8,000,000

 

[antsal]

700,00 = 14,000
4,000,000 = 140,000
8,000,000 = 280,000

 

[Marcelo Branco]

I'm not following you...

Why 4,000,000 = 140,000?

Shouldn't it be 4,000,000*0.04 = 160,000?

And 8,000,000*0,05 = 400,000

 

[antsal]

I'm not following you...

Why 4,000,000 = 140,000?

Shouldn't it be 4,000,000*0.04 = 160,000?

And 8,000,000*0,05 = 400,000

Thats right. The numbers I gave you are using the calculator (ie. the IF statement) thats not working properly, hence my need for assistance.

 

[Aladin Akyurek]

Thats right. The numbers I gave you are using the calculator (ie. the IF statement) thats not working properly, hence my need for assistance.

The numbers I asked for were what they should be, indeed not what the IF formula you have calculates.

Create a 2-column range like below:

0	0.02
1,000,000	0.035
3,000,000	0.04
5,000,000	0.05

<COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

Say in A2:B5 on a sheet called Admin. Name (using the Name Box) A2:A5 as TIERS, and B2:B5 as RATES.

Now you can invoke:

=LOOKUP(C7,TIERS,RATES)*C7

If you don't want to create a range, invoke the following formula instead:

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

 

[Marcelo Branco]

Edit
Aladin has already provided a solution

Maybe something like this

Create a Table in A1:B5 like below (i use comma as decimal separator)

A B C D E

Low Threshold	Rate		Value	Commission
0	0,02		700000	14000
1000001	0,035		4000000	160000
3000001	0,04		8000000	400000
5000001	0,05		1000000	20000

<tbody>
</tbody>

Enter the values in D2, D3....

Formula in E2
=D2*INDEX($B$2:$B$5,MATCH(D2,$A$2:$A$5))
copy down

M.

 

[antsal]

Will try the VLOOKUP and see if that works.

Thanks so much for your help.