Formula for Commission

[Guest]

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.

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

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

 

[JE McGimpsey]

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