#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bedford
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
topola
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bruno Campanini
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
topola
 
Posts: n/a
Default calculate ranges

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bedford
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Spreadsheet Won't Calculate Scott Excel Discussion (Misc queries) 0 September 29th 05 05:37 PM
how to calculate number ranges Lori Excel Worksheet Functions 2 May 2nd 05 05:05 PM
Problem with graph ranges No Such Luck Charts and Charting in Excel 6 December 3rd 04 02:09 PM
compare unique identifiers in multiple ranges bawilli_91125 Charts and Charting in Excel 1 November 30th 04 07:34 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 05:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"