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


--
spunkysezza
------------------------------------------------------------------------
spunkysezza's Profile: http://www.excelforum.com/member.php...o&userid=31921
View this thread: http://www.excelforum.com/showthread...hreadid=516475

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

"spunkysezza" wrote:


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


--
spunkysezza
------------------------------------------------------------------------
spunkysezza's Profile: http://www.excelforum.com/member.php...o&userid=31921
View this thread: http://www.excelforum.com/showthread...hreadid=516475

Or, for a more generic solution, look he

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

--
Kind regards,

Niek Otten

"Eddy Stan" wrote in message
...
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)))

"spunkysezza" wrote:


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


--
spunkysezza
------------------------------------------------------------------------
spunkysezza's Profile:
http://www.excelforum.com/member.php...o&userid=31921
View this thread:
http://www.excelforum.com/showthread...hreadid=516475

"spunkysezza" wrote:
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)

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


--
spunkysezza
------------------------------------------------------------------------
spunkysezza's Profile: http://www.excelforum.com/member.php...o&userid=31921
View this thread: http://www.excelforum.com/showthread...hreadid=516475