Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate ranges
=MIN(50000,A1)*50%+MIN(MAX(0,A1-50000)*40%,(100000-50000)*40%)+MAX(0,A1-100000)*30%
-- topola |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate ranges
|
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 |