commissions by range

[abryan]

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

 

[rsenn]

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

 

[Bob Phillips]

=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)

 

[JE McGimpsey]

As an alternative, see

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

 

[abryan]

JE McGimpsey, thanks for the idea - I put in the following per your sit
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 m
mac (brand new with brand new excel) crashes when i try to nest ifs

 

[abryan]

sorry, to clarify i should mention that for the sake of readability
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,00

 

[abryan]

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

 

[JE McGimpsey]

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