# Nested formula!

#### dunard2

##### New Member
Hello. Below is a not too terribly complicated nested if statement:

=IF(AND(D2>=55000,D2<=66000),IF(AND(C2="A"),K148,IF(AND(C2="B"),K149,IF(AND(C2="C"),K150,IF(AND(C2="D"),K151,IF(AND(C2="E"),K152,IF(AND(C2="F"),K153,0)))))))

What I need to do now is to nest two other nearly identical formulas into this one, but can’t quite seem to get the syntax correct.

The initial condition to be evaluated is the value of cell D2. So the second statement needs to account for a number that is >66000 and <=77000 while the third is for a number that is >77000 and <=88000. No numbers less than 55000 or greater than 88000 need to be evaluated.

The other differences are the reference cells that the formula calls when the “text” conditions are true. So in the case where D2=70000, “A” will reference M148 and in the third, depending on the number in D2, an “A” will reference O148, and so on.

Therefore, depending on the number in D2, the formula will travel down one of three possible logic paths to return a corresponding value contained in columns K, M, or O.

#### dreid1011

##### Well-known Member
Re: Help with a nested, nested, nested formula!

Welcome to the board.

Perhaps this:

=IF(AND(D2>=55000,D2<=66000),IF(C2="A",\$K\$148,IF(C2="B",\$K\$149,IF(C2="C",\$K\$150,IF(C2="D",\$K\$151,IF(C2="E",\$K\$152,IF(C2="F",\$K\$153,0)))))),IF(AND(D2>66000,D2<=77000),IF(C2="A",\$M\$148,IF(C2="B",\$M\$149,IF(C2="C",\$M\$150,IF(C2="D",\$M\$151,IF(C2="E",\$M\$152,IF(C2="F",\$M\$153,0)))))),IF(AND(D2>77000,D2<=88000),IF(C2="A",\$O\$148,IF(C2="B",\$O\$149,IF(C2="C",\$O\$150,IF(C2="D",\$O\$151,IF(C2="E",\$O\$152,IF(C2="F",\$O\$153,0)))))))))

The extra AND()'s were redundant as you were only checking 1 condition.

#### shg

##### MrExcel MVP
Re: Help with a nested, nested, nested formula!

Another way, perhaps:

Code:
``````=IF(MEDIAN(55000, D2, 66000) = D2, IFERROR(INDEX(K148:K153, FIND(C2, "ABCDEF")), 0),
IF(MEDIAN(66000, D2, 77000) = D2, IFERROR(INDEX(M148:M153, FIND(C2, "ABCDEF")), 0),
IF(MEDIAN(77000, D2, 88000) = D2, IFERROR(INDEX(O148:O153, FIND(C2, "ABCDEF")), 0))))``````

#### dunard2

##### New Member
Re: Help with a nested, nested, nested formula!

Works great - thank you very much!

#### mrwiley

##### Board Regular
Re: Help with a nested, nested, nested formula!

Going along with this I have a question -

I want to take 2 numbers - A B and find the percentage. B/A but if the percentage is greater than 105% want to subtract the percentage greater than 105%. Then multiply the percentage by 2 then adding it or subtracting it from the 105% to get a whole or negative number.

For example - B is 784 and A is 576 - 784/576=136.11% when I subtract 105% I get 31.11%. Now I take the 136.11%-31.11% *2 = 73.89%

I want to make this all in one cell, currently I am using 2 - Cell C and D - C is the result B/A and D I use

Code:
``=IF(IF((C4/B4)>1.05,D4-((C4/B4)-1.05)*2,D4)<0,(IF((C4/B4)>1.05,D4-((C4/B4)-1.05)*2,D4)),IF((C4/B4)>1.05,D4-((C4/B4)-1.05)*2,D4))``
Is this doable? I'd rather have the entire formula in C

Thanks

#### dreid1011

##### Well-known Member
Re: Help with a nested, nested, nested formula!

then adding it or subtracting it from the 105% to get a whole or negative number.
How do you determine if you need to add or subtract from the whole %?

For example - B is 784 and A is 576 - 784/576=136.11% when I subtract 105% I get 31.11%. Now I take the 136.11%-31.11% *2 = 73.89%
And it's not clear here... is the final addition/subtraction from 105% or the whole %?

#### FDibbins

##### Well-known Member
Re: Help with a nested, nested, nested formula!

Another version...
=IF(AND(D2>=55000,D2<=66000),IF(AND(C2="A"),K148,IF(AND(C2="B"),K149,IF(AND(C2="C"),K150,IF(AND(C2="D"),K151,IF(AND(C2="E"),K152,IF(AND(C2="F"),K153,0)))))))
=IF(AND(D2>=55000,D2<=66000),INDEX(\$K\$148:\$K\$153,MATCH(\$C\$2,{"a","b","c","d","e","f"},0)),0)

Combining those...
=IF(D2>=77000,INDEX(\$O\$148:\$O\$153,MATCH(\$C\$2,{"a","b","c","d","e","f"},0)),IF(D2>=66000,INDEX(\$m\$148:\$m\$153,MATCH(\$C\$2,{"a","b","c","d","e","f"},0)),INDEX(\$k\$148:\$k\$153,MATCH(\$C\$2,{"a","b","c","d","e","f"},0))))

#### mrwiley

##### Board Regular
Re: Help with a nested, nested, nested formula!

How do you determine if you need to add or subtract from the whole %?
My apologies - The goal is 100% but if the result is 105% it's OK but if it exceeds 105% then the difference is subtracted then multiplied by 2 then subtracted from the percentage.
Code:
``````[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Result[/TD]
[TD="class: xl63, width: 64"]>105%[/TD]
[TD="class: xl63, width: 64"]X2[/TD]
[TD="width: 64"]Total[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]108.66%[/TD]
[TD="class: xl63, align: right"]3.66%[/TD]
[TD="class: xl63, align: right"]7.32%[/TD]
[TD="class: xl63, align: right"]101.34%[/TD]
[/TR]
</tbody>[/TABLE]``````
And it's not clear here... is the final addition/subtraction from 105% or the whole %?

#### dreid1011

##### Well-known Member
Re: Help with a nested, nested, nested formula!

Perhaps this then:

=IF((B2/A2)>1.05,(B2/A2)-(((B2/A2)-1.05)*2),(B2/A2))

#### mrwiley

##### Board Regular
Re: Help with a nested, nested, nested formula!

Perhaps this then:

=IF((B2/A2)>1.05,(B2/A2)-(((B2/A2)-1.05)*2),(B2/A2))
thanks so much, I am not sure how I overlooked that simplicity. Thank you Thank you

1,082,369
Messages
5,365,049
Members
400,819
Latest member
Gossow

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...