Hello,

I am trying create a formula that will calculate commission. I would like a
single formula that will calculate $8.00 for the first 16 sales (sales 1 €“
16), $12.00 for the next five sales (sales 17 €“ 21) and $15.00 for each sale
at 22 or more.

So if I sold 23 units the commission will be $218.00.

The number of units sold will be in cell A1.

Thanks for the help.

For what is probably the definitive guide to calculating incremental
commisions, check this website:

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

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JR" wrote:

Hello,

I am trying create a formula that will calculate commission. I would like a
single formula that will calculate $8.00 for the first 16 sales (sales 1 €“
16), $12.00 for the next five sales (sales 17 €“ 21) and $15.00 for each sale
at 22 or more.

So if I sold 23 units the commission will be $218.00.

The number of units sold will be in cell A1.

Thanks for the help.

yea I looked at that before and it does not answer my question. I am going
to repost, please do not reply so I can get an actual answer from someone.

Thanks

"Ron Coderre" wrote:

For what is probably the definitive guide to calculating incremental
commisions, check this website:

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

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JR" wrote:

Hello,

I am trying create a formula that will calculate commission. I would like a
single formula that will calculate $8.00 for the first 16 sales (sales 1 €“
16), $12.00 for the next five sales (sales 17 €“ 21) and $15.00 for each sale
at 22 or more.

So if I sold 23 units the commission will be $218.00.

The number of units sold will be in cell A1.

Thanks for the help.

Well, if you had used the technique at the web page that Ron cited, you
would have come up with:

=SUMPRODUCT(--(A1-{0,16,21}0),A1-{0,16,21},{8,4,3})

but since that doesn't answer your question, I'm not sure what you're
looking for...

In article ,
JR wrote:

yea I looked at that before and it does not answer my question. I am going
to repost, please do not reply so I can get an actual answer from someone.

Another way of putting this is $8/unit, PLUS $4/unit for all units over 16,
PLUS $3/unit for all units over 21:
=8*A1 + 4*max(0,A1-16) + 3*max(0,A1-21)

"JR" wrote:

Hello,

I am trying create a formula that will calculate commission. I would like a
single formula that will calculate $8.00 for the first 16 sales (sales 1 €“
16), $12.00 for the next five sales (sales 17 €“ 21) and $15.00 for each sale
at 22 or more.

So if I sold 23 units the commission will be $218.00.

The number of units sold will be in cell A1.

Thanks for the help.