# Need help making a forumula!!!!

G

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

H

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

G

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

G

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

G

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

J

#### Jesse_Norris

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

Dan said:
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

G

#### Guest

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

Jesse_Norris said:
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]

G

#### Guest

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

paul said:
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]

S

#### Sandy Mann

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

H

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

J

#### joeu2004

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

S

#### 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
Replace@mailinator with @tiscali.co.uk

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

R

#### 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
Replace@mailinator with @tiscali.co.uk

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

S

#### 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
Replace@mailinator with @tiscali.co.uk

RagDyeR said:
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
Replace@mailinator with @tiscali.co.uk

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

R

#### 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
Replace@mailinator with @tiscali.co.uk

RagDyeR said:
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
Replace@mailinator with @tiscali.co.uk

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

S

#### 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
Replace@mailinator with @tiscali.co.uk

Sandy Mann said:
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 said:
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
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.