Need help making a forumula!!!!

[Guest]

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]

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

 

[Guest]

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 (e-mail address removed) if you need to!

Thanks much Henry!!!

 

[Guest]

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

 

[Guest]

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

 

[Jesse_Norris]

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

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

dan

[/QUOTE][/QUOTE][/QUOTE]


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

 

[Guest]

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

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

[/QUOTE][/QUOTE]


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
[/QUOTE]

 

[Guest]

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

the problem is that the first 100 units remain at 2.00 and then go to 2.25.....

[/QUOTE]


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
[/QUOTE][/QUOTE]

 

[Sandy Mann]

Dan,

If the first option then try:

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

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Henry]

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

 

[joeu2004]

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

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.

 

[Sandy Mann]

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+(A1>100)*((INT(A1/100)*((A1>100)*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")+(A1>100)*((INT(A1/100)*((A1>100)*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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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.

 

[RagDyeR]

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

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+(A1>100)*((INT(A1/100)*((A1>100)*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")+(A1>100)*((INT(A1/100)*((A1>100)*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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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.

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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+(A1>100)*((INT(A1/100)*((A1>100)*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")+(A1>100)*((INT(A1/100)*((A1>100)*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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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.

 

[RagDyeR]

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

Regards,

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

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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+(A1>100)*((INT(A1/100)*((A1>100)*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")+(A1>100)*((INT(A1/100)*((A1>100)*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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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.

 

[Sandy Mann]

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

=MIN(A1,100)*2+(A1>100)*((INT(A1/100)*((A1>100)*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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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+(A1>100)*((INT(A1/100)*((A1>100)*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")+(A1>100)*((INT(A1/100)*((A1>100)*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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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