figuring commissions based on gross sales

[Guest]

I want to be able to plug in a gross sales number and have a formula spit out
the amount of commission to be paid to that sales person. However, their
production is tiered and they get paid a different commision based on the
level they are at, for example the plan is as follows:
0-5000 in sales = 20%
5001-10000 in sales = 30%
10001-15000 in sales = 40%

15000 in sales = 50%

If a sales person closes 18000 in gross sales they get paid 20% on the first
5000 in sales, 30% on the next 5000 in sales, 40% on the next 5000 in sales
and the remaining 3000 in sales is paid at 50%. In this example the sales
person will earn $6000. I want to be able to plug in the gross sales figure
and have the commission figured for me.

thanks

 

[Guest]

Try something like this:

With a sales amount in A1

This formula returns the commission amount:
B1:
=SUM((A1>{0,5000,10000,15000})*(A1-{0,5000,10000,15000})*({0.2,0.1,0.1,0.1}))

If you want a table driven solution:
Enter these values in E1:F4....
0 20%
5000 10%
10000 10%
15000 10%

B1: =SUMPRODUCT((A1>$E$1:$E$4)*(A1-$E$1:$E$4)*($F$1:$F$4))

Note: Those formulas start with 20% as the base commission and calculate the
incremental commissions at each step.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP