calculating commissions for a range of values

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

[JE McGimpsey]

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

[bpeltzer]

You could reformulate the issue as:
..5% of sales
PLUS .25% of sales over 100
PLUS .25% of sales above 150
Which translates into =a1*0.5% + max(0,a1-100)*.25% + max(0,a1-150)*.25%.
--Bruce

"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".
>
>
> --
> abryan
> ------------------------------------------------------------------------
> abryan's Profile: http://www.excelforum.com/member.php...o&userid=29118
> View this thread: http://www.excelforum.com/showthread...hreadid=488361
>
>