Nested iF, AND formula.

[aiki100]

Hello everyone -

I would love some help with this formula if someone is willing to give it a go:


=IF(BG2="RT",IF(AND($AR2="x",$AZ2="L",$J3<= $BF2),"Y",IF(BG2="RT",IF(AND($AR2="x",$AZ2="S",$I3>= $BF2),"Y",IF(BG2="T",IF(AND($AR2="x",$AZ2="L",$J3<= $BH2),"Y",IF(BG="T",IF(AND($AR2="x",$AZ2="S",$I3>= $BH2),"Y",IF(BG2="TEF",IF(AND($AR2="x",$AZ2="L",$J3<= $BH2),"Y",IF(BG="TEF",IF(AND($AR2="x",$AZ2="S",$I3>= $BH2),"Y",""))))))))))))


This cell returns in a "Y" if BG2= "RT". If anything other than "RT"(including an empty cell) is in BG2, it returns FALSE.


I would like the formula to return the appropriate results based on BG2 containing any of, T, RT, or TEF. This cell will always contain one of these three.


Thanks much for your insight and assistance!

 

[jorismoerings]

Hi,

Aren't you missing something in this formula? see my red addtitions

=IF(BG2="RT",IF(AND($AR2="x",$AZ2="L",$J3<= $BF2),"Y",IF(BG2="RT",IF(AND($AR2="x",$AZ2="S",$I3>= $BF2),"Y",IF(BG2="T",IF(AND($AR2="x",$AZ2="L",$J3<= $BH2),"Y",IF(BG2="T",IF(AND($AR2="x",$AZ2="S",$I3>= $BH2),"Y",IF(BG2="TEF",IF(AND($AR2="x",$AZ2="L",$J3<= $BH2),"Y",IF(BG2="TEF",IF(AND($AR2="x",$AZ2="S",$I3>= $BH2),"Y",""))))))))))))

 

[aiki100]

Thank you jorismoerings -

This is a signal example of why you should never work on excel late at night after a long day! Thanks for catching that!

However, the issue with this formula still stands:

The cell with this formula returns in a "Y" if BG2= "RT". If anything other than "RT" (including an empty cell) is in BG2, it returns FALSE.

Any thoughts, please?

And thank you again for your willingness to look this over!

 

[Special-K99]

=IF(BG2="RT",IF(AND($AR2="x",$AZ2="L",$J3<= $BF2),"Y",IF(BG2="RT",IF(AND($AR2="x",$AZ2="S",$I3>= $BF2),"Y",IF(BG2="T",IF(AND($AR2="x",$AZ2="L",$J3<= $BH2),"Y",IF(BG2="T",IF(AND($AR2="x",$AZ2="S",$I3>= $BH2),"Y",IF(BG2="TEF",IF(AND($AR2="x",$AZ2="L",$J3<= $BH2),"Y",IF(BG2="TEF",IF(AND($AR2="x",$AZ2="S",$I3>= $BH2),"Y",""))))))))))),"SOME OTHER VALUE")

FALSE means you havent supplied a value if the conditions fails

 

[Special-K99]

I think you can shorten that formula to this

=IF(AR2="X",IF(A22="L",IF(J3<=IF(OR(BG2="RT",BG2="T",BG2="TEF"),BF2,BH2),"Y",""),IF(I3>=IF(OR(BG2="RT",BG2="T",BG2="TEF"),BF2,BH2),"Y","")),"")

This assumes AR22 will only be L or S and that BG2 will only be one of RT, T, TEF

 

[aiki100]

Hi Special-K99 -

Wow. Yes, the shortened formula works! (I changed AR22 to AZ2, which is what the cell is...) but otherwise this seems to work just fine, and is a FAR more elegant solution...!

I am MOST appreciative of your help here and will study your solution so that I can hopefully field this type of issue myself in the future.

Thank you again for taking this time to tackle this - we hope you have a fine day, indeed!

 

[Special-K99]

Thanks

I ended up making some notes on the conditions like this

BG2=RT      AR2=X     AZ2=L     J3<=BF2     Y
BG2=RT      AR2=X     AZ2=S     I3>=BF2     Y
BG2=T       AR2=X     AZ2=L     J3<=BH2     Y
BG2=T       AR2=X     AZ2=S     I3>=BH2     Y
BG2=TEF     AR2=X     AZ2=L     J3<=BH2     Y
BG2=TEF     AR2=X     AZ2=S     I3>=BH2     Y

 

[aiki100]

ah yes - I see...that's a good way to break it down. I do something similar, but yours is cleaner.

Thank you again - have a good one!