"Bob Bedford" wrote in message ...
I've a range table for calculating commissions:

0-50000 50%
500001-100000 40%
+100000 30%

Now, I'd like to create a table with automatic calculation like

5000 2500 (as it's 50%)
50000 25000 (also 50%)
75000 35000 (50000 at 50% and 25000 at 40%)
and so on.

How to create this formula ? given an amount, how to calculate the
commission ?

Thanks for help.

Given such a table:

0 50000 50%
50000 100000 40%
100000 1000000 30%
1000000 10000000 20%
10000000 100000000 10%

and s on.

Name first column as Linf
Name second column as Lsup
Name third column Commissions

The following formula gives the commissions for value in A1:

{IF(AND(ISNUMBER(A1),A1),(SUM((OFFSET(Lsup,0,0,SUM ((Linf<A1)*(A1<=Lsup)*
(ROW(Linf)-ROW(OFFSET(Linf,,,1))+1)))-OFFSET(Linf,0,0,SUM((Linf<A1)*(A1<=Lsup)*
(ROW(Linf)-ROW(OFFSET(Linf,,,1))+1))))*OFFSET(Commissions,0,0 ,SUM((Linf<A1)*(A1<=Lsup)*
(ROW(Linf)-ROW(OFFSET(Linf,,,1))+1))))-(OFFSET(Lsup,SUM((Linf<A1)*(A1<=Lsup)*
(ROW(Linf)-ROW(OFFSET(Linf,,,1))+1))-1,0)-A1)*(OFFSET(Commissions,SUM((Linf<A1)*(A1<=Lsup)*
(ROW(Linf)-ROW(OFFSET(Linf,,,1))+1))-1,0))),"")}


Should you have a lot of lines, a VBA macro would be sure
much better.
Let me know.

Ciao
Bruno