Not sure what type of Formula to use...

[Guest]

I am working on an "experience" bonus for my sales reps. For every new sale
they make, the salesman's commision goes up .1% (one 10th of a percent) up to
their 50th sale which would be 5.0%. Can someone help me with a formula for
this?
Thank you very much!!!

 

[Guest]

Let's say your Total Sales Amount is in cell A1. Use this formula to find
the bonus:

=MIN(A1,50)*.1%

HTH,
Elkar

 

[Guest]

A couple of ways to approach this, depending on how you record your sales.

Assume that each sale is recorded by amount in column A starting at row 1, as
$1000.00
$495.33
$1040.21
etc
In B1 you could enter this formula:
=MIN(COUNT(A$1:A1),50)*0.1
Extend that down the page and with each numeric entry in column a, the
result will increase.
If you're recording sales as text "Shirts", "Pants" etc for entries in
column a, use COUNTA( instead of just COUNT(
COUNT() only counts cells with numeric entries
COUNTA() counts cells with pretty much anything in them (not empty).

If this bonus is to be added to a basic minimum bonus amount, then you could
modify the formula to add it in:
=MIN(COUNT(A$1:A1),50)*0.1 + .10
assuming a 10% minimum bonus amount.

If you just have a single cell (A1) that has the total number of sales in
it, then
=MIN(A1,50)*.01

MIN() function says display/use the smallest of the numbers within the
parenthesis. So if A1 has 51 or 1001 in it, 50 is the largest value that
would be used.

 

[Guest]

Hi Kim,

=(0.1*30)/100
assuming 30 sales

if the sales are in a same column i.é.
Col A
1 $100.00
2 $200.00
3 $150.00

you could use =(0.1*COUNTA(A2:A100))/100

hth
regards from Brazil
Marcelo




"Kim" escreveu:

 

[Bob Phillips]

a simple

=num_sales*.1%

if you want to cap it

=MIN(num_sales*.1%,max%)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)