Commission Formula

[Gladys]

Basically what i need to do is add individual commisions for each
salesperson. if they sell 1-11 untis hey receive 25% commission. On the 12
sale, they receive 30% comission for all of their sales. Therefore, i must
go back and add in the extra 5% of commission for the first 11 sales. on the
16th sale they start to receive 35% commission...i miust then go back and add
on the additional 5% for the first 15 sales. Lastly, on the 26th sale they
receive 40% commission and then once again i must add the additional 5% for
all of the previous sales. Thus, there commission is retroactive
My problem is that not all sales will land in the same cell, as see some
cells do not count as a unit sold, and thus i am having trouble figuring out
a way to make one spreadsheet that will be compatable for all salesman.

Thanks in advance.

[pinmaster]

This should help you with the first part:

assuming A1 = units, B1 = Sales, then in C1:

=B1*LOOKUP(A1,{1,12,16,26},{0.25,0.3,0.35,0.4})

as for the second part, we would need more details.

HTH
JG

"Gladys" wrote:

> Basically what i need to do is add individual commisions for each
> salesperson. if they sell 1-11 untis hey receive 25% commission. On the 12
> sale, they receive 30% comission for all of their sales. Therefore, i must
> go back and add in the extra 5% of commission for the first 11 sales. on the
> 16th sale they start to receive 35% commission...i miust then go back and add
> on the additional 5% for the first 15 sales. Lastly, on the 26th sale they
> receive 40% commission and then once again i must add the additional 5% for
> all of the previous sales. Thus, there commission is retroactive
> My problem is that not all sales will land in the same cell, as see some
> cells do not count as a unit sold, and thus i am having trouble figuring out
> a way to make one spreadsheet that will be compatable for all salesman.
>
> Thanks in advance.
>
>

[Sandy Mann]

Extending pinmaster's formula:

With the list of the different salespersons' names in Column A, the list of
items being sold in Column B and the cost of that particular item in C1,
try:

=SUMPRODUCT((A1:A1000="Jim")*(B1:B1000="Item")*C1*(LOOKUP(SUMPRODUCT((A1:A1000="Jim")*(B1:B1000="Item")),{1,12,16,26},{0.25,0.3,0.35,0.4})))


--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk


"pinmaster" <[email protected]> wrote in message
news:[email protected]...
> This should help you with the first part:
>
> assuming A1 = units, B1 = Sales, then in C1:
>
> =B1*LOOKUP(A1,{1,12,16,26},{0.25,0.3,0.35,0.4})
>
> as for the second part, we would need more details.
>
> HTH
> JG
>
> "Gladys" wrote:
>
>> Basically what i need to do is add individual commisions for each
>> salesperson. if they sell 1-11 untis hey receive 25% commission. On the
>> 12
>> sale, they receive 30% comission for all of their sales. Therefore, i
>> must
>> go back and add in the extra 5% of commission for the first 11 sales. on
>> the
>> 16th sale they start to receive 35% commission...i miust then go back and
>> add
>> on the additional 5% for the first 15 sales. Lastly, on the 26th sale
>> they
>> receive 40% commission and then once again i must add the additional 5%
>> for
>> all of the previous sales. Thus, there commission is retroactive
>> My problem is that not all sales will land in the same cell, as see some
>> cells do not count as a unit sold, and thus i am having trouble figuring
>> out
>> a way to make one spreadsheet that will be compatable for all salesman.
>>
>> Thanks in advance.
>>
>>

[JE McGimpsey]

See

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

It's impossible for me to guess what you mean by your last paragraph.

In article <[email protected]>,
Gladys <[email protected]> wrote:

> Basically what i need to do is add individual commisions for each
> salesperson. if they sell 1-11 untis hey receive 25% commission. On the 12
> sale, they receive 30% comission for all of their sales. Therefore, i must
> go back and add in the extra 5% of commission for the first 11 sales. on the
> 16th sale they start to receive 35% commission...i miust then go back and add
> on the additional 5% for the first 15 sales. Lastly, on the 26th sale they
> receive 40% commission and then once again i must add the additional 5% for
> all of the previous sales. Thus, there commission is retroactive
> My problem is that not all sales will land in the same cell, as see some
> cells do not count as a unit sold, and thus i am having trouble figuring out
> a way to make one spreadsheet that will be compatable for all salesman.
>
> Thanks in advance.