Home 
Search 
Today's Posts 
#1




calculate ranges
I've a range table for calculating commissions:
050000 50% 500001100000 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. 
#2




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: 050000 50% 500001100000 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. 
#3




calculate ranges
Hi Bob
With Value in A1, enter in B1 =MIN(50000,A1)*50%+MAX(0,A150000)*40%+MAX(0,A1100000)*30% Regards Roger Govier Bob Bedford wrote: I've a range table for calculating commissions: 050000 50% 500001100000 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. 
#4




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




calculate ranges
"Bob Bedford" wrote in message ...
I've a range table for calculating commissions: 050000 50% 500001100000 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 
#6




calculate ranges
=MIN(50000,A1)*50%+MIN(MAX(0,A150000)*40%,(10000050000)*40%)+MAX(0,A1100000)*30%
 topola 
#7




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) 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Spreadsheet Won't Calculate  Excel Discussion (Misc queries)  
how to calculate number ranges  Excel Worksheet Functions  
Problem with graph ranges  Charts and Charting in Excel  
compare unique identifiers in multiple ranges  Charts and Charting in Excel  
Named dynamic ranges, copied worksheets and graph source data  Charts and Charting in Excel 