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)


"abryan" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[JE McGimpsey]

As an alternative, see

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

In article <[email protected]>,
abryan <[email protected]> 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".

[abryan]

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]

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]

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


In article <[email protected]>,
abryan <[email protected]> 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%})