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

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

=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

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".

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

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

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

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%})