Forum Discussion

cestrada's avatar
cestrada
Copper Contributor
Aug 30, 2019

Help with Excel using IF and AND and OR and NOT functions

Hi, I'm trying to write a formula that incorporates the IF function and uses the OR, AND and NOT features.   I want the formula to read:   IF cell G5="DS" or "DT" AND cell AF5 does not equal "FT10...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 30, 2019

cestrada 

That's like

=IF(AND(OR(G5="DS",G5="DT"),OR(AF5<>"FT10",AF5<>"FC10",AF5<>"TXTX",AF5<>"RSTK")),-S5,0)

if translate your text into formula

cestrada's avatar
cestrada
Copper Contributor
Aug 30, 2019

SergeiBaklan 

 

I'm so sorry, I spoke too soon, the formula isn't working.  It seems to be ignoring the second part of the equation with the <>"FT10","FC10", etc...

 

I've attached a sample with the formula in column Z.  All highlighted cells should be 0, yet they're returning -S5 value.  

 

I may not have explained what I want to see very well.  The highlighted cells are either DS or DT however, they have FT10, FC10, TXTX or RSTK in column AF, so I want them to be 0.  

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Aug 30, 2019

    cestrada 

    Just the boolean logic.

    =OR(G5="DS",G5="DT")*AND(AF5<>"FT10",AF5<>"FC10",AF5<>"TXTX",AF5<>"RSTK")*S5

     

    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      Aug 30, 2019

      Detlef_Lewin 

      It needs only a small tweak...

      =OR(G9="DS",G9="DT")*AND(AF9<>"FT10",AF9<>"FC10",AF9<>"TXTX",AF9<>"RSTK")*-S9

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Aug 30, 2019

    cestrada 

    The second OR should be replaced with AND in your formula, try this...

     

    =IF(AND(OR(G5="DS",G5="DT"),AND(AF5<>"FT10",AF5<>"FC10",AF5<>"TXTX",AF5<>"RSTK")),-S5,0)

     

     

Resources