I Need Help With A Complex Formula

[Jonathan Cheek]

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.

 

[Ms-Exl-Learner]

=IF(A16="","",IF(A16>=2000000,3%,IF(AND(A16>=1500000,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)))))))

Remember to Click Yes, if this post helps!

 

[Dave]

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.

 

[David Biddulph]

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>=1500000,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))))))) to =IF(A16="","",IF(A16>=2000000,3%,IF(A16>=1500000,4%,IF(A16>=1000000,4.5%,IF(A16>=600000,5%,IF(A16>=450000,5.5%,6%))))))--David Biddulph"Ms-Exl-Learner" <[email protected]> wrote in messageRemember 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>=450000,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.

 

[Ms-Exl-Learner]

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.

 

[Bob Phillips]

Try

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

 

[Jonathan Cheek]

Thank You all! All your solutions worked very well.