ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   calculate ranges (https://www.excelbanter.com/excel-worksheet-functions/57222-calculate-ranges.html)

Bob Bedford

calculate ranges
 
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

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

--
Kind regards,

Niek Otten

"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.




Roger Govier

calculate ranges
 
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


Bob Bedford wrote:
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.


topola

calculate ranges
 
=IF(A1100000,45000+(A1-100000)*0.3,IF(A150000,25000+(A1-50000)*0.4,A1*0.5))
--
HTH
Tomek Polak, http://vba.blog.onet.pl


Bruno Campanini

calculate ranges
 
"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

topola

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


Bob Bedford

calculate ranges
 
"Niek Otten" a écrit dans le message de news:
...
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)




All times are GMT +1. The time now is 05:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com