Hello!!! Here is what im trying to do!

I am trying to set up a commission scale for promoters that pays them $2 per
unit sold if they sell under 100 units.

If they sell 101 - 200 units id like to pay them $2.25 per unit.
201 - 300 units they get $2.5 per unit

How can I do this in a sensible way that organizes the commissions and shows
me the sales person's total commissions.

Dan,

Not clear what you want.

Sales of 101 units:

Either 100@ $2.00 +1 @ $2.25 =$202.25

Or 101 @ $2.25 = $227.25

Henry


"Dan Lieberman" <Dan wrote in message
...
Hello!!! Here is what im trying to do!

I am trying to set up a commission scale for promoters that pays them $2
per
unit sold if they sell under 100 units.

If they sell 101 - 200 units id like to pay them $2.25 per unit.
201 - 300 units they get $2.5 per unit

How can I do this in a sensible way that organizes the commissions and
shows
me the sales person's total commissions.

Hi Henry.
First option

I need to set it up so i can simply input in how many units this person has
sold in total, and then have the worksheet show me the commissions amounts..

100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So all i want to do is input a # and have it tell me how much commission
this person has earned...

please email me direct at if you need to!

Thanks much Henry!!!



"Henry" wrote:

Dan,

Not clear what you want.

Sales of 101 units:

Either 100@ $2.00 +1 @ $2.25 =$202.25

Or 101 @ $2.25 = $227.25

Henry


"Dan Lieberman" <Dan wrote in message
...
Hello!!! Here is what im trying to do!

I am trying to set up a commission scale for promoters that pays them $2
per
unit sold if they sell under 100 units.

If they sell 101 - 200 units id like to pay them $2.25 per unit.
201 - 300 units they get $2.5 per unit

How can I do this in a sensible way that organizes the commissions and
shows
me the sales person's total commissions.

Dan, try this...

With unit numbers beginning in Cell A2 put this formula in cell B2
=IF(A2<=100,A2*2,IF(A2<=200,A2*2.25,A2*2.5))


"Dan Lieberman" wrote:

Hi Henry.
First option

I need to set it up so i can simply input in how many units this person has
sold in total, and then have the worksheet show me the commissions amounts..

100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So all i want to do is input a # and have it tell me how much commission
this person has earned...

please email me direct at if you need to!

Thanks much Henry!!!



"Henry" wrote:

Dan,

Not clear what you want.

Sales of 101 units:

Either 100@ $2.00 +1 @ $2.25 =$202.25

Or 101 @ $2.25 = $227.25

Henry


"Dan Lieberman" <Dan wrote in message
...
Hello!!! Here is what im trying to do!

I am trying to set up a commission scale for promoters that pays them $2
per
unit sold if they sell under 100 units.

If they sell 101 - 200 units id like to pay them $2.25 per unit.
201 - 300 units they get $2.5 per unit

How can I do this in a sensible way that organizes the commissions and
shows
me the sales person's total commissions.

Wow thats an interesting equation. I didnt know Excel could do that kinda
stuff!
If I put in 101 units, I got 227.25 so i dont think its working just right
yet!! Going in the right direction though!!
thanks xgirl!!!
any ideas how to correct that??

dan


"xgirl" wrote:

Dan, try this...

With unit numbers beginning in Cell A2 put this formula in cell B2
=IF(A2<=100,A2*2,IF(A2<=200,A2*2.25,A2*2.5))


"Dan Lieberman" wrote:

Hi Henry.
First option

I need to set it up so i can simply input in how many units this person has
sold in total, and then have the worksheet show me the commissions amounts..

100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So all i want to do is input a # and have it tell me how much commission
this person has earned...

please email me direct at if you need to!

Thanks much Henry!!!



"Henry" wrote:

Dan,

Not clear what you want.

Sales of 101 units:

Either 100@ $2.00 +1 @ $2.25 =$202.25

Or 101 @ $2.25 = $227.25

Henry


"Dan Lieberman" <Dan wrote in message
...
Hello!!! Here is what im trying to do!

I am trying to set up a commission scale for promoters that pays them $2
per
unit sold if they sell under 100 units.

If they sell 101 - 200 units id like to pay them $2.25 per unit.
201 - 300 units they get $2.5 per unit

How can I do this in a sensible way that organizes the commissions and
shows
me the sales person's total commissions.

Xgirl
=IF(A2<=100,A2*2,IF(A2<=200,A2*2.25,A2*2.5))

Dan Lieberman Wrote:
Wow thats an interesting equation. I didnt know Excel could do that
kinda
stuff!
If I put in 101 units, I got 227.25 so i dont think its working just
right
yet!! Going in the right direction though!!
thanks xgirl!!!
any ideas how to correct that??

dan

[/color][/color][/color]


hi dan i cant find a problem with this formula, if you use your
caculator you will find that 101*2.25 = 227.25, I have checked and
dubble checked the calulation.
well done Xgirl
Regards,
Jesse


--
Jesse_Norris
------------------------------------------------------------------------
Jesse_Norris's Profile: http://www.excelforum.com/member.php...o&userid=25546
View this thread: http://www.excelforum.com/showthread...hreadid=401339

the problem is that the first 100 units remain at 2.00 and then go to 2.25.....
--
paul
remove nospam for email addy!



"Jesse_Norris" wrote:


Xgirl
=IF(A2<=100,A2*2,IF(A2<=200,A2*2.25,A2*2.5))

Dan Lieberman Wrote:
Wow thats an interesting equation. I didnt know Excel could do that
kinda
stuff!
If I put in 101 units, I got 227.25 so i dont think its working just
right
yet!! Going in the right direction though!!
thanks xgirl!!!
any ideas how to correct that??

dan

[/color][/color]


hi dan i cant find a problem with this formula, if you use your
caculator you will find that 101*2.25 = 227.25, I have checked and
dubble checked the calulation.
well done Xgirl
Regards,
Jesse


--
Jesse_Norris
------------------------------------------------------------------------
Jesse_Norris's Profile: http://www.excelforum.com/member.php...o&userid=25546
View this thread: http://www.excelforum.com/showthread...hreadid=401339

[/color]

try this
=IF(A1<=100,A1*2,IF(A1<=200,A1*2.5,IF(A1201,A1*3) ))

"paul" wrote:

the problem is that the first 100 units remain at 2.00 and then go to 2.25.....
--
paul
remove nospam for email addy!



"Jesse_Norris" wrote:


Xgirl
=IF(A2<=100,A2*2,IF(A2<=200,A2*2.25,A2*2.5))

Dan Lieberman Wrote:
Wow thats an interesting equation. I didnt know Excel could do that
kinda
stuff!
If I put in 101 units, I got 227.25 so i dont think its working just
right
yet!! Going in the right direction though!!
thanks xgirl!!!
any ideas how to correct that??

dan

[/color]


hi dan i cant find a problem with this formula, if you use your
caculator you will find that 101*2.25 = 227.25, I have checked and
dubble checked the calulation.
well done Xgirl
Regards,
Jesse


--
Jesse_Norris
------------------------------------------------------------------------
Jesse_Norris's Profile: http://www.excelforum.com/member.php...o&userid=25546
View this thread: http://www.excelforum.com/showthread...hreadid=401339

[/color][/color]

Dan,

If the first option then try:

=A1*2+SUM((A1{100,200})*(A1-{100,200})*0.25)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Dan Lieberman" wrote in message
...
Hi Henry.
First option

I need to set it up so i can simply input in how many units this person
has
sold in total, and then have the worksheet show me the commissions
amounts..

100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So all i want to do is input a # and have it tell me how much commission
this person has earned...

please email me direct at if you need to!

Thanks much Henry!!!



"Henry" wrote:

Dan,

Not clear what you want.

Sales of 101 units:

Either 100@ $2.00 +1 @ $2.25 =$202.25

Or 101 @ $2.25 = $227.25

Henry


"Dan Lieberman" <Dan wrote in
message
...
Hello!!! Here is what im trying to do!

I am trying to set up a commission scale for promoters that pays them
$2
per
unit sold if they sell under 100 units.

If they sell 101 - 200 units id like to pay them $2.25 per unit.
201 - 300 units they get $2.5 per unit

How can I do this in a sensible way that organizes the commissions and
shows
me the sales person's total commissions.

Dan,

=IF(A1<101,A1*2,IF(A1<201,(200+(A1-100)*2.25),IF(A1=201,(425+(A1-200)*2.5))))

works for me.

As it stands, it will give $2.50 for each unit over 200.

If you want it limited to 300, what is the commission for the 301st unit?

Henry

"Dan Lieberman" wrote in message
...
Hi Henry.
First option

I need to set it up so i can simply input in how many units this person
has
sold in total, and then have the worksheet show me the commissions
amounts..

100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So all i want to do is input a # and have it tell me how much commission
this person has earned...

please email me direct at if you need to!

Thanks much Henry!!!



"Henry" wrote:

Dan,

Not clear what you want.

Sales of 101 units:

Either 100@ $2.00 +1 @ $2.25 =$202.25

Or 101 @ $2.25 = $227.25

Henry


"Dan Lieberman" <Dan wrote in
message
...
Hello!!! Here is what im trying to do!

I am trying to set up a commission scale for promoters that pays them
$2
per
unit sold if they sell under 100 units.

If they sell 101 - 200 units id like to pay them $2.25 per unit.
201 - 300 units they get $2.5 per unit

How can I do this in a sensible way that organizes the commissions and
shows
me the sales person's total commissions.

"Henry" wrote:
Not clear what you want. Sales of 101 units:
Either 100@ $2.00 +1 @ $2.25 =$202.25
Or 101 @ $2.25 = $227.25

Dan Lieberman wrote:
First option
[....]
100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So what you mean to say is: $2.00 for the first 100,
$2.25 for the second 100, and $2.50 for the third 100.

What about the fourth 100, etc?

If you mean: $2.50 for any number over 200, you could
use the following formula:

2*min(a1,100) + 2.25*max(0,min(a1-100,100)) + 2.50*max(0,a1-200)

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.

Alternatively, if there is a reasonable limit (e.g,
"no one could sell more than 500"), you could extend
the formula above. For example:

2*min(a1,100) + 2.25*max(0,min(a1-100,100))
+ 2.50*max(0,min(a1-200,100)) + 2.75*max(0,min(a1-300,100))
+ 3.00*max(0,a1-400)

Note: This pays $3.00 for any number over 400.

wrote in message
ups.com...

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.


I don't think it is what the OP wants, (or if it is he has a very generous
employer), but just for the fun of it:

=MIN(A1,100)*2+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+MOD(A1,100)*(INT(A1/100)*0.25+2))

increases by 0.25 for each 100 over 100.

or with explanations included:

=N("Calculate 1st 100")+MIN(A1,100)*2+N("Calculate all other
100's")+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+N("Calculate
remaining 10's & units items")+MOD(A1,100)*(INT(A1/100)*0.25+2))
--
Regards


Sandy

Replace@mailinator with @tiscali.co.uk


wrote in message
ups.com...
"Henry" wrote:
Not clear what you want. Sales of 101 units:
Either 100@ $2.00 +1 @ $2.25 =$202.25
Or 101 @ $2.25 = $227.25

Dan Lieberman wrote:
First option
[....]
100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So what you mean to say is: $2.00 for the first 100,
$2.25 for the second 100, and $2.50 for the third 100.

What about the fourth 100, etc?

If you mean: $2.50 for any number over 200, you could
use the following formula:

2*min(a1,100) + 2.25*max(0,min(a1-100,100)) + 2.50*max(0,a1-200)

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.

Alternatively, if there is a reasonable limit (e.g,
"no one could sell more than 500"), you could extend
the formula above. For example:

2*min(a1,100) + 2.25*max(0,min(a1-100,100))
+ 2.50*max(0,min(a1-200,100)) + 2.75*max(0,min(a1-300,100))
+ 3.00*max(0,a1-400)

Note: This pays $3.00 for any number over 400.

Try this:
Up to 500 items @ 0.25 increase per 100:

=SUMPRODUCT((A1{0,100,200,300,400,500})*(A1-{0,100,200,300,400,500})*{2,0.2
5,0.25,0.25,0.25,0.25})

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Sandy Mann" wrote in message
...
wrote in message
ups.com...

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.


I don't think it is what the OP wants, (or if it is he has a very generous
employer), but just for the fun of it:

=MIN(A1,100)*2+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+M
OD(A1,100)*(INT(A1/100)*0.25+2))

increases by 0.25 for each 100 over 100.

or with explanations included:

=N("Calculate 1st 100")+MIN(A1,100)*2+N("Calculate all other
100's")+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+N("Calcu
late
remaining 10's & units items")+MOD(A1,100)*(INT(A1/100)*0.25+2))
--
Regards


Sandy

Replace@mailinator with @tiscali.co.uk


wrote in message
ups.com...
"Henry" wrote:
Not clear what you want. Sales of 101 units:
Either 100@ $2.00 +1 @ $2.25 =$202.25
Or 101 @ $2.25 = $227.25

Dan Lieberman wrote:
First option
[....]
100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So what you mean to say is: $2.00 for the first 100,
$2.25 for the second 100, and $2.50 for the third 100.

What about the fourth 100, etc?

If you mean: $2.50 for any number over 200, you could
use the following formula:

2*min(a1,100) + 2.25*max(0,min(a1-100,100)) + 2.50*max(0,a1-200)

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.

Alternatively, if there is a reasonable limit (e.g,
"no one could sell more than 500"), you could extend
the formula above. For example:

2*min(a1,100) + 2.25*max(0,min(a1-100,100))
+ 2.50*max(0,min(a1-200,100)) + 2.75*max(0,min(a1-300,100))
+ 3.00*max(0,a1-400)

Note: This pays $3.00 for any number over 400.

Yes RD that works and it points out that there is an error in my formula
whenever the 10's and units go over 49 - too little testing!. It is much
more elegant too!

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"RagDyeR" wrote in message
...
Try this:
Up to 500 items @ 0.25 increase per 100:

=SUMPRODUCT((A1{0,100,200,300,400,500})*(A1-{0,100,200,300,400,500})*{2,0.2
5,0.25,0.25,0.25,0.25})

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Sandy Mann" wrote in message
...
wrote in message
ups.com...

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.


I don't think it is what the OP wants, (or if it is he has a very generous
employer), but just for the fun of it:

=MIN(A1,100)*2+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+M
OD(A1,100)*(INT(A1/100)*0.25+2))

increases by 0.25 for each 100 over 100.

or with explanations included:

=N("Calculate 1st 100")+MIN(A1,100)*2+N("Calculate all other
100's")+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+N("Calcu
late
remaining 10's & units items")+MOD(A1,100)*(INT(A1/100)*0.25+2))
--
Regards


Sandy

Replace@mailinator with @tiscali.co.uk


wrote in message
ups.com...
"Henry" wrote:
Not clear what you want. Sales of 101 units:
Either 100@ $2.00 +1 @ $2.25 =$202.25
Or 101 @ $2.25 = $227.25

Dan Lieberman wrote:
First option
[....]
100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So what you mean to say is: $2.00 for the first 100,
$2.25 for the second 100, and $2.50 for the third 100.

What about the fourth 100, etc?

If you mean: $2.50 for any number over 200, you could
use the following formula:

2*min(a1,100) + 2.25*max(0,min(a1-100,100)) + 2.50*max(0,a1-200)

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.

Alternatively, if there is a reasonable limit (e.g,
"no one could sell more than 500"), you could extend
the formula above. For example:

2*min(a1,100) + 2.25*max(0,min(a1-100,100))
+ 2.50*max(0,min(a1-200,100)) + 2.75*max(0,min(a1-300,100))
+ 3.00*max(0,a1-400)

Note: This pays $3.00 for any number over 400.

Credit where credit is due.
Learned that one from John McGimpsey.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Sandy Mann" wrote in message
...
Yes RD that works and it points out that there is an error in my formula
whenever the 10's and units go over 49 - too little testing!. It is much
more elegant too!

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"RagDyeR" wrote in message
...
Try this:
Up to 500 items @ 0.25 increase per 100:


=SUMPRODUCT((A1{0,100,200,300,400,500})*(A1-{0,100,200,300,400,500})*{2,0.2
5,0.25,0.25,0.25,0.25})

--

Regards,

RD
--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------------
--
-------------------

"Sandy Mann" wrote in message
...
wrote in message
ups.com...

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.


I don't think it is what the OP wants, (or if it is he has a very generous
employer), but just for the fun of it:


=MIN(A1,100)*2+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+M
OD(A1,100)*(INT(A1/100)*0.25+2))

increases by 0.25 for each 100 over 100.

or with explanations included:

=N("Calculate 1st 100")+MIN(A1,100)*2+N("Calculate all other

100's")+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+N("Calcu
late
remaining 10's & units items")+MOD(A1,100)*(INT(A1/100)*0.25+2))
--
Regards


Sandy

Replace@mailinator with @tiscali.co.uk


wrote in message
ups.com...
"Henry" wrote:
Not clear what you want. Sales of 101 units:
Either 100@ $2.00 +1 @ $2.25 =$202.25
Or 101 @ $2.25 = $227.25

Dan Lieberman wrote:
First option
[....]
100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So what you mean to say is: $2.00 for the first 100,
$2.25 for the second 100, and $2.50 for the third 100.

What about the fourth 100, etc?

If you mean: $2.50 for any number over 200, you could
use the following formula:

2*min(a1,100) + 2.25*max(0,min(a1-100,100)) + 2.50*max(0,a1-200)

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.

Alternatively, if there is a reasonable limit (e.g,
"no one could sell more than 500"), you could extend
the formula above. For example:

2*min(a1,100) + 2.25*max(0,min(a1-100,100))
+ 2.50*max(0,min(a1-200,100)) + 2.75*max(0,min(a1-300,100))
+ 3.00*max(0,a1-400)

Note: This pays $3.00 for any number over 400.

The "over 49 " gave me the clue. I should have used ROUNDDOWN instead of
ROUND:

=MIN(A1,100)*2+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUNDDOWN(A1-100,-2)+MOD(A1,100)*(INT(A1/100)*0.25+2))

I post it for the record if for no other purpose and the fact that it is not
limited to any specific number.

By the way RD your formula works correctly up to 600.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Yes RD that works and it points out that there is an error in my formula
whenever the 10's and units go over 49 - too little testing!. It is much
more elegant too!

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"RagDyeR" wrote in message
...
Try this:
Up to 500 items @ 0.25 increase per 100:

=SUMPRODUCT((A1{0,100,200,300,400,500})*(A1-{0,100,200,300,400,500})*{2,0.2
5,0.25,0.25,0.25,0.25})

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Sandy Mann" wrote in message
...
wrote in message
ups.com...

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.


I don't think it is what the OP wants, (or if it is he has a very
generous
employer), but just for the fun of it:

=MIN(A1,100)*2+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+M
OD(A1,100)*(INT(A1/100)*0.25+2))

increases by 0.25 for each 100 over 100.

or with explanations included:

=N("Calculate 1st 100")+MIN(A1,100)*2+N("Calculate all other
100's")+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+N("Calcu
late
remaining 10's & units items")+MOD(A1,100)*(INT(A1/100)*0.25+2))
--
Regards


Sandy

Replace@mailinator with @tiscali.co.uk


wrote in message
ups.com...
"Henry" wrote:
Not clear what you want. Sales of 101 units:
Either 100@ $2.00 +1 @ $2.25 =$202.25
Or 101 @ $2.25 = $227.25

Dan Lieberman wrote:
First option
[....]
100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So what you mean to say is: $2.00 for the first 100,
$2.25 for the second 100, and $2.50 for the third 100.

What about the fourth 100, etc?

If you mean: $2.50 for any number over 200, you could
use the following formula:

2*min(a1,100) + 2.25*max(0,min(a1-100,100)) + 2.50*max(0,a1-200)

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.

Alternatively, if there is a reasonable limit (e.g,
"no one could sell more than 500"), you could extend
the formula above. For example:

2*min(a1,100) + 2.25*max(0,min(a1-100,100))
+ 2.50*max(0,min(a1-200,100)) + 2.75*max(0,min(a1-300,100))
+ 3.00*max(0,a1-400)

Note: This pays $3.00 for any number over 400.