I Need Help With A Complex Formula - microsoft.public.excel.worksheet.functions

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>=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! -------------------- (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.

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.

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" <Ms.Exl.Learner@gmail.com> wrote in messagenews:EB49A830-86BA-4A32-B524-C0157BBA2B3C@microsoft.com...>=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!>> --------------------> (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.

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>=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" <Ms.Exl.Learner@gmail.com> wrote in messagenews:EB49A830-86BA-4A32-B524-C0157BBA2B3C@microsoft.com...>=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!>> --------------------> (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. > > . >

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 Cheek@discussions.microsoft.com> wrote in message news:2FF7A4A3-2C86-4386-A252-AF0A40DCCA2D@microsoft.com... >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.

Similiar Articles:

How do I access a previous version of my Excel document ...
I have made changes to my document, but need to be able to retrieve complex formula's back from a ... somewhere else, or e-mailed a copy to someone, that might help ...

how to calculate compound interest ? - microsoft.public.mac.office ...
Need formula to calculate bi-monthly pay dates - microsoft.public ... Hi folks, Would appreciate some help in locating a formula to calculate compound interest on an ...

complex lookup - microsoft.public.excel.worksheet.functions ...
I have a lookup table that I need to reference ... Show quoted text - This isnt going to help you much but the formula ... Complex value lookup? (Excel 2003) - microsoft ...

How to fix Compatibility error - microsoft.public.excel.misc ...
I need to keep it in 2003 format because some ... I have a complex worksheet with > macros and complex formulas ... Help pls - error on code and I dont know how to fix ...

Nested IF(OR(AND) Statement with Two AND Conditions - microsoft ...
... could use some assistance with a complex ... Any help you can provide would be ... I understand the logic of your equation and > it will deliver the results I need if ...

Complex Counter - microsoft.public.excel.misc
> I will need to copy the formula in H2 down to H521. ... need help with look-up and time rage Utf 4 111. Unique project names with complex array formulas Utf 8 120

What Excel formula converts hourly wage to annual salary ...
I need help creating a simple formula that converts an hourly wage to an annual salary. ...

Excel formula to incrementally increase a value by 1 every 365 day ...
I need help with correct formula to take a starting date from one cell and place a formula in another cell that will increase the number by one for ...

ActiveCell.FormulaR1C1 - microsoft.public.excel.misc
I need help.... The following statement is being used in a do loop. ActiveCell ... recording a macro the cnt1 changes from nothing to 9 then to 18 but the formula ...

Excel formula won't work (if, match, iserr, vlookup ...
On Feb 12, 1:40=A0am, midget <mid...@discussions.microsoft.com> wrote: > Hi > I need help with the following formula: > =3Dif(=3Diserr(=3Dmatch(a2,LIST,0))=3D0 ...

Need help creating a complex formula. - Microsoft Answers
I am creating an invoice in Excel and need to create a formula. Ok, I have 3 columns, lets say A1,B1,C1. A1 is for an amount, B1 is a percentage, and C1 is a total.

Need Help Troubleshooting: Complex Formula W/Nested IF - Microsoft ...
I have to write a complex formula for a table in my excel worksheet. It is based upon the colums [Status]; which describes an employee's employment status (ex. FT or PT).

I need help with this question can anyone help me? ...continues ...
Write the formula of the complex ion formed between the ligand and ...

Excel Formulas Help from Mr Excel - MrExcel.com | Excel Resources ...
Complex Formula writing; Complex formula.... complex really need help from you. Complicated Formula Precedents; Complicated Formula, need help! Complicated IF(AND & IF(OR ...

Calculate Compound Interest: Formula with examples and practice ...
Homework Help; Algebra; Geometry; Trigonometry; Scientific Calc ... To calculate compound interest use the formula below. In the formula, A represents the final amount ...

7/27/2012 12:35:53 AM