Nested formula!

[dunard2]

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]

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]

Re: Help with a nested, nested, nested formula!

Another way, perhaps:

=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]

Re: Help with a nested, nested, nested formula!

Works great - thank you very much!

 

[mrwiley]

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

=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]

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]

Re: Help with a nested, nested, nested formula!

Another version...
your 1st formula...
=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]

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.

[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]

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]

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