Remember Me? November 25th 05, 10:07 AM posted to microsoft.public.excel.worksheet.functions
 Bob Bedford Posts: n/a 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. November 25th 05, 10:14 AM posted to microsoft.public.excel.worksheet.functions
 Niek Otten Posts: n/a 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. November 25th 05, 10:48 AM posted to microsoft.public.excel.worksheet.functions
 Roger Govier Posts: n/a 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. November 25th 05, 11:36 AM posted to microsoft.public.excel.worksheet.functions
 topola Posts: n/a 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 November 25th 05, 11:36 AM posted to microsoft.public.excel.worksheet.functions
 Bruno Campanini Posts: n/a 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 November 25th 05, 11:40 AM posted to microsoft.public.excel.worksheet.functions
 topola Posts: n/a calculate ranges

=MIN(50000,A1)*50%+MIN(MAX(0,A1-50000)*40%,(100000-50000)*40%)+MAX(0,A1-100000)*30%
--
topola November 25th 05, 12:03 PM posted to microsoft.public.excel.worksheet.functions
 Bob Bedford Posts: n/a 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)

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Scott Excel Discussion (Misc queries) 0 September 29th 05 05:37 PM Lori Excel Worksheet Functions 2 May 2nd 05 05:05 PM No Such Luck Charts and Charting in Excel 6 December 3rd 04 02:09 PM bawilli_91125 Charts and Charting in Excel 1 November 30th 04 07:34 PM WP Charts and Charting in Excel 1 November 28th 04 06:19 PM

All times are GMT +1. The time now is 11:45 PM. Copyright ©2004-2019 ExcelBanter.