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 here:

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

--
Kind regards,

Niek Otten

"Eddy Stan" <(E-Mail Removed)> wrote in message
news:6519E83E-C78F-444E-8AEF-(E-Mail Removed)...
> 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