MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### I Need Help With A Complex Formula

• Follow

```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.
```
 0

```=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.
```
 0

```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.
```
 0

```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.

```
 0

```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.
>
> .
>
```
 0

```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.

```
 0

```Thank You all! All your solutions worked very well.

```
 0

6 Replies
128 Views

Similiar Articles:

7/27/2012 12:35:53 AM