Calculating sales commision HELP!!!!!!!

[spunkysezza]

Hi everyone;

I really need help, I've looked everywhere and I can't seem to find
anything that has all that my boss is wanting to do! Typical


My boss is looking for a formula that will be able to calculate the
following.

Sales Commision by rep

E.G.
3% of sales up to 99.99% $_____
5% of sales up to 100% - 109.99% $______
10% of sales up to 110% - above $______

It will be based on the targets that he sets the reps per month.

If anyone has any ideas I would be so gratefull.

Thanks for taking the time to think my request through

Sarah

 

[Eddy Stan]

try this..

A1 Target B1 12000
A2 Sales B2 13150
A3 % B3 109.58%
commission B4 657.5

at b4 put
=IF(B3<=99.99%,B2*3%,IF(AND(B3>=100%,B3<=109.99%),B2*5%,IF(B3>=110%,B2*10%,0)))

 

[Niek Otten]

Or, for a more generic solution, look here:

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

 

[joeu2004]

My boss is looking for a formula that will be able to
calculate the following.
Sales Commision by rep[.] E.G.
3% of sales up to 99.99% $_____
5% of sales up to 100% - 109.99% $______
10% of sales up to 110% - above $______

If A1 contains pecentage increase in sales [1]:

=if(a1<100%, 3%, if(a1<110%, 5%, 10%))

Note: Consequently, a sales increase of 99.992% will get
a 3% commission. That falls into a gray area in the rules
above. I suspect that what I wrote matches your true
intention.


-----
[1] Percentage increase in sales can be computed as
follows, if B1 contains current sales and B2 contains
previous sales:

=b1/b2 - 1

Format as Percentage with 2 decimal places. To be
consistent with the rule stated above, you might want
to round percentage to 2 decimal places:

=round(b1/b2 - 1, 4)

 

[spunkysezza]

Hi Everyone

Thank you so much for getting back to me so quickly. I'm going to give
all the advice a try until I get what my manager is looking for. I'll
let you know how I go.

Thanks again

Cheers

Sarah