What Formula Should I Use?

[Kim]

First Question:

If one of my sales reps sell
2 seperate newsletters, they get 1%
3 seperate newseltters = 2%
4 seperate newseltters = 3%
5 seperate newseltters = 4%
6 seperate newseltters = 5%
7 seperate newseltters = 6%
8 seperate newseltters = 7%
9 or more seperate newseltters = 8%
It caps out at 8%

Can you please help me with a formula for this?

Second and LAST question
If they sell
2 versions = 0.5%
3 versions = 1.0%
4 versions = 1.5%
5 versions = 2.0%
6 versions = 2.5%
7 versions = 3.0%
8 versions = 3.5%
9 versions = 4.0%
Capping out at 4%

I can't tell you what a help this site is and everyone out there taking
their time to help me out!
Thank you, Thank you, Thank you!!!!! You have made my life so much easier
today!

 

[Marcelo]

Hi Kim,

you can create a table as

Col A Col B
2 1%
3 2%
....
9 8%

and use a vllokup to find the % of bonuses

hth
regards from Brazil
Marcelo

"Kim" escreveu:

 

[Kim]

Marcelo - I have no idea what a vllokup is. I'm really, really new to excel.

 

[Toppers]

Kim,

First one

=Min(8,A1) where A1 are sales

To allow for sales of 1

=If(A1>1,Min(8,A1) ,0)


For second

=MIN(4,(A1-1)*0.5)

To allow for sales of 1

=If(A1>1,MIN(4,(A1-1)*0.5) ,0)

HTH

 

[Bearacade]

First one:

=IF(A1<10
LOOKUP(A1,{1,2,3,4,5,7,8,9;0.01,0.02,0.03,0.04,0.05,0.06,0.07,0.08})
0.08)

Second one:

=IF(A1<10
LOOKUP(A1,{1,2,3,4,5,7,8,9;0.005,0.01,0.015,0.02,0.025,0.03,0.035,0.04})
0.04)

This is assuming it's not 0 or blan

 

[Kent Finnell]

Marcelo - I have no idea what a vllokup is. I'm really, really new to
excel.

Marcelo made a slight typo. That should be "vlookup" for Vertical Lookup.
Use the formula help for a simple example.

 

[Marcelo]

perfect thanks Kent

"Kent Finnell" escreveu:

Marcelo made a slight typo. That should be "vlookup" for Vertical Lookup.
Use the formula help for a simple example.

 

[Toppers]

Typos:

=Min(8,(A1-1)) *0.01 to give %

=MIN(4,(A1-1)*0.5)*0.01 to give %

Change IF statements accordingly.

 

[Kim]

Hi Topper-
The below formulas you provided me with work perfectly unless I put a -0- in
the cell, then it either gives me a negative 1% or 1/2 %. How can I fix this
so when I put a zero in the cell it gives me a 0% rather than a negative 1%?
Thank you very much!
Kim

 

[Bob Phillips]

=MIN(8,MAX(0,(A1-1))) *0.01

=MIN(4,(MAX(0,A1-1))*0.5)*0.01

--

HTH

Bob Phillips

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