I have been trying to make a formula that will out put a certain percentage
for the numbers I input. for example if i put 2,000,000 in Cell A16 I want it
give me 3% in Cell B16. This is the complex formula that i came up with

=IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4%,IF(AND(A16=1000000,A16<1500000),4.5%,IF(AND( A16=600000,A16<1000000),5%,IF(AND(A16=450000,A16 <600000),5.5%,IF(A16<450000,6%,0))))))

Every time i use this formula, I doesn't give me the right percentages if I
input anything under 150,000,000. For example if I input the number 600,000
then i should return me the percentage of 5%. However when i attempt this,
the output percentage will always be 4%. to me this means that only
this portion is functional:

=IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4%

If anyone can find out what I'm doing wrong. I would mean a world of help
for me.

=IF(A16="","",IF(A16=2000000,3%,IF(AND(A16=15000 00,A16<2000000),4%,IF(AND(A16=1000000,A16<1500000 ),4.5%,IF(AND(A16=600000,A16<1000000),5%,IF(AND(A 16=450000,A16<600000),5.5%,IF(A16<450000,6%,0)))) )))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Jonathan Cheek" wrote:

I have been trying to make a formula that will out put a certain percentage
for the numbers I input. for example if i put 2,000,000 in Cell A16 I want it
give me 3% in Cell B16. This is the complex formula that i came up with

=IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4%,IF(AND(A16=1000000,A16<1500000),4.5%,IF(AND( A16=600000,A16<1000000),5%,IF(AND(A16=450000,A16 <600000),5.5%,IF(A16<450000,6%,0))))))

Every time i use this formula, I doesn't give me the right percentages if I
input anything under 150,000,000. For example if I input the number 600,000
then i should return me the percentage of 5%. However when i attempt this,
the output percentage will always be 4%. to me this means that only
this portion is functional:

=IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4%

If anyone can find out what I'm doing wrong. I would mean a world of help
for me.

There seem to be a number of unnecessary tests there.
You've tested for =2000000, so you don't then need to test for <2000000, &
similarly for the later tests.

You can simplify
=IF(A16="","",IF(A16=2000000,3%,IF(AND(A16=15000 00,A16<2000000),4%,IF(AND(A16=1000000,A16<1500000 ),4.5%,IF(AND(A16=600000,A16<1000000),5%,IF(AND(A 16=450000,A16<600000),5.5%,IF(A16<450000,6%,0)))) ))) to =IF(A16="","",IF(A16=2000000,3%,IF(A16=1500000,4 %,IF(A16=1000000,4.5%,IF(A16=600000,5%,IF(A16=4 50000,5.5%,6%))))))--David Biddulph"Ms-Exl-Learner" wrote in ...=IF(A16="","",IF(A16 =2000000,3%,IF(AND(A16=1500000,A16<2000000),4%,I F(AND(A16=1000000,A16<1500000),4.5%,IF(AND(A16=6 00000,A16<1000000),5%,IF(AND(A16=450000,A16<60000 0),5.5%,IF(A16<450000,6%,0))))))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Jonathan Cheek" wrote: I have been trying to make a formula that will out put a certainpercentage for the numbers I input. for example if i put 2,000,000 in Cell A16 Iwant it give me 3% in Cell B16. This is the complex formula that i came up with=IF(A16=2000000,3%,IF(AND(A16=150000,A16 <2000000),4%,IF(AND(A16=1000000,A16<1500000),4.5% ,IF(AND(A16=600000,A16<1000000),5%,IF(AND(A16=45 0000,A16<600000),5.5%,IF(A16<450000,6%,0)))))) Every time i use this formula, I doesn't give me the right percentages ifI input anything under 150,000,000. For example if I input the number600,000 then i should return me the percentage of 5%. However when i attemptthis, the output percentage will always be 4%. to me this means that only this portion is functional: =IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4% If anyone can find out what I'm doing wrong. I would mean a world of help for me.

Yes David Sir you are right, I have just modified the OP's formula and given
the same. After seeing your post only I come to know that it can be
simplified.

--------------------
(Ms-Exl-Learner)
--------------------


"David Biddulph" wrote:

There seem to be a number of unnecessary tests there.
You've tested for =2000000, so you don't then need to test for <2000000, &
similarly for the later tests.

You can simplify
=IF(A16="","",IF(A16=2000000,3%,IF(AND(A16=15000 00,A16<2000000),4%,IF(AND(A16=1000000,A16<1500000 ),4.5%,IF(AND(A16=600000,A16<1000000),5%,IF(AND(A 16=450000,A16<600000),5.5%,IF(A16<450000,6%,0)))) ))) to =IF(A16="","",IF(A16=2000000,3%,IF(A16=1500000,4 %,IF(A16=1000000,4.5%,IF(A16=600000,5%,IF(A16=4 50000,5.5%,6%))))))--David Biddulph"Ms-Exl-Learner" wrote in ...=IF(A16="","",IF(A16 =2000000,3%,IF(AND(A16=1500000,A16<2000000),4%,I F(AND(A16=1000000,A16<1500000),4.5%,IF(AND(A16=6 00000,A16<1000000),5%,IF(AND(A16=450000,A16<60000 0),5.5%,IF(A16<450000,6%,0))))))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Jonathan Cheek" wrote: I have been trying to make a formula that will out put a certainpercentage for the numbers I input. for example if i put 2,000,000 in Cell A16 Iwant it give me 3% in Cell B16. This is
the complex formula that i came up with=IF(A16=2000000,3%,IF(AND(A16=150000,A16 <2000000),4%,IF(AND(A16=1000000,A16<1500000),4.5% ,IF(AND(A16=600000,A16<1000000),5%,IF(AND(A16=45 0000,A16<600000),5.5%,IF(A16<450000,6%,0)))))) Every time i use this formula, I doesn't give me the right percentages ifI input anything under 150,000,000. For example if I input the number600,000 then i should return me the percentage of 5%. However when i attemptthis, the output percentage will always be 4%. to me this means that only this portion is functional: =IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4% If anyone can find out what I'm doing wrong. I would mean a world of help for me.

.

Hi Jonathin,
I think you have a zero missing from your second IF thingy.
You have 150000, and I think it should be 1500000
Regards - Dave.

"Jonathan Cheek" wrote:

I have been trying to make a formula that will out put a certain percentage
for the numbers I input. for example if i put 2,000,000 in Cell A16 I want it
give me 3% in Cell B16. This is the complex formula that i came up with

=IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4%,IF(AND(A16=1000000,A16<1500000),4.5%,IF(AND( A16=600000,A16<1000000),5%,IF(AND(A16=450000,A16 <600000),5.5%,IF(A16<450000,6%,0))))))

Every time i use this formula, I doesn't give me the right percentages if I
input anything under 150,000,000. For example if I input the number 600,000
then i should return me the percentage of 5%. However when i attempt this,
the output percentage will always be 4%. to me this means that only
this portion is functional:

=IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4%

If anyone can find out what I'm doing wrong. I would mean a world of help
for me.

Try

=IF(A16="","",LOOKUP(A16,{0,450000,600000,1000000, 1500000,2000000},{0.06,0.055,0.05,0.045,0.04,0.03} ))


---
HTH

Bob Phillips

"Jonathan Cheek" <Jonathan wrote in message
...
I have been trying to make a formula that will out put a certain percentage
for the numbers I input. for example if i put 2,000,000 in Cell A16 I want
it
give me 3% in Cell B16. This is the complex formula that i came up with

=IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4%,IF(AND(A16=1000000,A16<1500000),4.5%,IF(AND( A16=600000,A16<1000000),5%,IF(AND(A16=450000,A16 <600000),5.5%,IF(A16<450000,6%,0))))))

Every time i use this formula, I doesn't give me the right percentages if
I
input anything under 150,000,000. For example if I input the number
600,000
then i should return me the percentage of 5%. However when i attempt this,
the output percentage will always be 4%. to me this means that only
this portion is functional:

=IF(A16=2000000,3%,IF(AND(A16=150000,A16<2000000 ),4%

If anyone can find out what I'm doing wrong. I would mean a world of help
for me.

Thank You all! All your solutions worked very well.

Hello Jonathan,

You have committed a simple and banal mistake in the formula: instead of 1500000 you has written 150000. Has missed one 0.
Anything terrible. Such happens, when you work with the big number of zero.
So, I have corrected it and have simplified the formula:

=IF (A16 =2000000,3 %, IF (A16 =1500000,4 %, IF (A16 =1000000,4.5 %, IF (A16 =600000,5 %, IF (A16 =450000,5.5 %, 6 %)))))

Yours faithfully.