The below formulas work perfectly unless I put a zero (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


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

Question #2:
=MIN(4,(A1-1)*0.5)*0.01 to give %

First Question:
If one of my sales reps sell
2 separate newsletters, they get 1%
3 separate newsletters = 2%
4 separate newsletters = 3%
5 separate newsletters = 4%
6 separate newsletters = 5%
7 separate newsletters = 6%
8 separate newsletters = 7%
9 or more separate newsletters = 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%

Try these:

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

=MAX(0,MIN(4,(A1-1)*.5)*.01)

HTH,
Elkar


"Kim" wrote:

The below formulas work perfectly unless I put a zero (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


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

Question #2:
=MIN(4,(A1-1)*0.5)*0.01 to give %

First Question:
If one of my sales reps sell
2 separate newsletters, they get 1%
3 separate newsletters = 2%
4 separate newsletters = 3%
5 separate newsletters = 4%
6 separate newsletters = 5%
7 separate newsletters = 6%
8 separate newsletters = 7%
9 or more separate newsletters = 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%

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

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

"Kim" wrote:

The below formulas work perfectly unless I put a zero (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


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

Question #2:
=MIN(4,(A1-1)*0.5)*0.01 to give %

First Question:
If one of my sales reps sell
2 separate newsletters, they get 1%
3 separate newsletters = 2%
4 separate newsletters = 3%
5 separate newsletters = 4%
6 separate newsletters = 5%
7 separate newsletters = 6%
8 separate newsletters = 7%
9 or more separate newsletters = 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%

Like it!

"Elkar" wrote:

Try these:

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

=MAX(0,MIN(4,(A1-1)*.5)*.01)

HTH,
Elkar


"Kim" wrote:

The below formulas work perfectly unless I put a zero (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


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

Question #2:
=MIN(4,(A1-1)*0.5)*0.01 to give %

First Question:
If one of my sales reps sell
2 separate newsletters, they get 1%
3 separate newsletters = 2%
4 separate newsletters = 3%
5 separate newsletters = 4%
6 separate newsletters = 5%
7 separate newsletters = 6%
8 separate newsletters = 7%
9 or more separate newsletters = 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%

Question 1:
=IF(A1<=1,0,MIN(8,(A1-1)))*0.01

Question 2:
=IF(A1<=1,0,MIN(4,(A1-1)*0.5)*0.01)

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Kim" wrote:

The below formulas work perfectly unless I put a zero (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


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

Question #2:
=MIN(4,(A1-1)*0.5)*0.01 to give %

First Question:
If one of my sales reps sell
2 separate newsletters, they get 1%
3 separate newsletters = 2%
4 separate newsletters = 3%
5 separate newsletters = 4%
6 separate newsletters = 5%
7 separate newsletters = 6%
8 separate newsletters = 7%
9 or more separate newsletters = 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%