A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

commissions by range



 
 
Thread Tools Display Modes
  #1  
Old November 26th, 2005, 05:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default commissions by range


Hello, I have read the threads about similar problems on this site, but
nothing seems to work for me so perhaps I am doing something wrong.

I need to calculate commission varying by range:

.5% of sales up to 100
.75% of sales from 100 to 150
1% of sales above 150

I have tried using the IF function but can't figure out if there is a
way to signify "if less than x AND greater than y, then z".


--
abryan
------------------------------------------------------------------------
abryan's Profile: http://www.excelforum.com/member.php...o&userid=29118
View this thread: http://www.excelforum.com/showthread...hreadid=488360

  #2  
Old November 26th, 2005, 05:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default commissions by range


Assume the sales data is in cell A1.


=IF(A1=100,0.005*A1,IF(A1=150,0.5+0.0075*(A1-100),0.875+0.01*(A1-150)))


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=488360

  #3  
Old November 26th, 2005, 05:47 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default commissions by range

=MIN(A1,100)*5%+MIN(50,MAX(A1-100,0))*75%+MAX(A1-150,0)*1%


--

HTH

RP
(remove nothere from the email address if mailing direct)


"abryan" wrote in
message ...

Hello, I have read the threads about similar problems on this site, but
nothing seems to work for me so perhaps I am doing something wrong.

I need to calculate commission varying by range:

5% of sales up to 100
75% of sales from 100 to 150
1% of sales above 150

I have tried using the IF function but can't figure out if there is a
way to signify "if less than x AND greater than y, then z".


--
abryan
------------------------------------------------------------------------
abryan's Profile:

http://www.excelforum.com/member.php...o&userid=29118
View this thread: http://www.excelforum.com/showthread...hreadid=488360



  #4  
Old November 26th, 2005, 05:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default commissions by range

As an alternative, see

http://www.mcgimpsey.com/excel/variablerate.html

In article ,
abryan wrote:

Hello, I have read the threads about similar problems on this site, but
nothing seems to work for me so perhaps I am doing something wrong.

I need to calculate commission varying by range:

.5% of sales up to 100
.75% of sales from 100 to 150
1% of sales above 150

I have tried using the IF function but can't figure out if there is a
way to signify "if less than x AND greater than y, then z".

  #5  
Old November 26th, 2005, 06:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default commissions by range


JE McGimpsey, thanks for the idea - I put in the following per your site
but it returns an error...can anyone clarify what the error is?

=SUMPRODUCT(--(D7{0;3000000;5000000}),(D7-{0;3000000;5000000}),{.5%;.75%;1%})


rsenn, that is the strategy i had been trying but for some reason my
mac (brand new with brand new excel) crashes when i try to nest ifs.


--
abryan
------------------------------------------------------------------------
abryan's Profile: http://www.excelforum.com/member.php...o&userid=29118
View this thread: http://www.excelforum.com/showthread...hreadid=488360

  #6  
Old November 26th, 2005, 06:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default commissions by range


sorry, to clarify i should mention that for the sake of readability i
had used the figures 100 and 150 as stand-ins for the actual figures,
which are 3,000,000 and 5,000,000. not the best idea in retrospect.

the actual setup is:
.5% up to 3,000,000
.75% between 3,000,000 and 5,000,000
1% above 5,000,000


--
abryan
------------------------------------------------------------------------
abryan's Profile: http://www.excelforum.com/member.php...o&userid=29118
View this thread: http://www.excelforum.com/showthread...hreadid=488360

  #7  
Old November 26th, 2005, 06:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default commissions by range


I think i got it to work using Bob Phillips' formula. Thanks to all.


--
abryan
------------------------------------------------------------------------
abryan's Profile: http://www.excelforum.com/member.php...o&userid=29118
View this thread: http://www.excelforum.com/showthread...hreadid=488360

  #8  
Old November 27th, 2005, 12:42 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default commissions by range

You need to have the *change* in rate in your third argument, expressed
as a decimal. Try:

=SUMPRODUCT(--(D7{0;3000000;5000000}),(D7-{0;3000000;5000000}),
{0.005;0.0025;0.0025})


In article ,
abryan wrote:

JE McGimpsey, thanks for the idea - I put in the following per your site
but it returns an error...can anyone clarify what the error is?

=SUMPRODUCT(--(D7{0;3000000;5000000}),(D7-{0;3000000;5000000}),{.5%;.75%;1%})

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting 1 June 22nd, 2005 12:24 PM
Question about using Find with a Range object Max Moor Formatting Long Documents 11 May 16th, 2005 04:47 PM
Can a formula check for a certain value in a range? Lee IT General Discussion 3 April 8th, 2005 07:36 AM
Formulas with named ranges with 2 or more range areas agarwaldvk Worksheet Functions 1 September 7th, 2004 07:33 AM
is there a formula that can count a range of cells with text? Frank Kabel Worksheet Functions 0 March 11th, 2004 09:04 PM


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


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