calculate ranges

[Bob Bedford]

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.

 

[Niek Otten]

http://www.cpearson.com/excel/pricing.htm

 

[Roger Govier]

Hi Bob

With Value in A1, enter in B1
=MIN(50000,A1)*50%+MAX(0,A1-50000)*40%+MAX(0,A1-100000)*30%

Regards

Roger Govier

 

[topola]

=IF(A1>100000,45000+(A1-100000)*0.3,IF(A1>50000,25000+(A1-50000)*0.4,A1*0.5))

 

[Bruno Campanini]

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

 

[topola]

=MIN(50000,A1)*50%+MIN(MAX(0,A1-50000)*40%,(100000-50000)*40%)+MAX(0,A1-100000)*30%

 

[Bob Bedford]

http://www.cpearson.com/excel/pricing.htm

Thanks Niek, but this formula doesn't seem to work, i've an error and this
formula return #value. (and yes I did validate with ctrl+shift+enter)