Nested formula!

dunard2

New Member
Joined
Oct 9, 2017
Messages
2
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
Joined
Jun 4, 2015
Messages
2,126
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
Joined
May 7, 2008
Messages
21,718
Office Version
2010
Platform
Windows
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
Joined
Oct 9, 2017
Messages
2
Re: Help with a nested, nested, nested formula!

Works great - thank you very much!
 

mrwiley

Board Regular
Joined
Sep 10, 2012
Messages
56
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
Joined
Jun 4, 2015
Messages
2,126
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
Joined
Feb 16, 2013
Messages
6,723
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

Board Regular
Joined
Sep 10, 2012
Messages
56
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
Joined
Jun 4, 2015
Messages
2,126
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
Joined
Sep 10, 2012
Messages
56
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top