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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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))))
 
Upvote 0
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
 
Upvote 0
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 %?
 
Upvote 0
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))))
 
Upvote 0
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 %?
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top