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. |
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. |
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. |
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 |
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 |
calculate ranges
=MIN(50000,A1)*50%+MIN(MAX(0,A1-50000)*40%,(100000-50000)*40%)+MAX(0,A1-100000)*30%
-- topola |
calculate ranges
|
All times are GMT +1. The time now is 05:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com