Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-831805%22%20slang%3D%22en-US%22%3EHelp%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831805%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20trying%20to%20write%20a%20formula%20that%20incorporates%20the%20IF%20function%20and%20uses%20the%20OR%2C%20AND%20and%20NOT%20features.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20formula%20to%20read%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20cell%20G5%3D%22DS%22%20or%20%22DT%22%20AND%20cell%20AF5%20does%20not%20equal%20%22FT10%22%20or%20%22FC10%22%20or%20%22TXTX%22%20or%20%22RSTK%22%2C%20then%20return%20the%20negative%20value%20in%20cell%20S5%20otherwise%2C%20return%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-831805%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831834%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401256%22%20target%3D%22_blank%22%3E%40cestrada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(AND(OR(G5%3D%22DS%22%2CG5%3D%22DT%22)%2COR(AF5%26lt%3B%26gt%3B%22FT10%22%2CAF5%26lt%3B%26gt%3B%22FC10%22%2CAF5%26lt%3B%26gt%3B%22TXTX%22%2CAF5%26lt%3B%26gt%3B%22RSTK%22))%2C-S5%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20translate%20your%20text%20into%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831879%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20help%2C%20it%20works%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831943%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401256%22%20target%3D%22_blank%22%3E%40cestrada%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831945%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20so%20sorry%2C%20I%20spoke%20too%20soon%2C%20the%20formula%20isn't%20working.%26nbsp%3B%20It%20seems%20to%20be%20ignoring%20the%20second%20part%20of%20the%20equation%20with%20the%20%26lt%3B%26gt%3B%22FT10%22%2C%22FC10%22%2C%20etc...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20sample%20with%20the%20formula%20in%20column%20Z.%26nbsp%3B%20All%20highlighted%20cells%20should%20be%200%2C%20yet%20they're%20returning%20-S5%20value.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20may%20not%20have%20explained%20what%20I%20want%20to%20see%20very%20well.%26nbsp%3B%20The%20highlighted%20cells%20are%20either%20DS%20or%20DT%20however%2C%20they%20have%20FT10%2C%20FC10%2C%20TXTX%20or%20RSTK%20in%20column%20AF%2C%20so%20I%20want%20them%20to%20be%200.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831960%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831960%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401256%22%20target%3D%22_blank%22%3E%40cestrada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20OR%20should%20be%20replaced%20with%20AND%20in%20your%20formula%2C%20try%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(AND(OR(G5%3D%22DS%22%2CG5%3D%22DT%22)%2CAND(AF5%26lt%3B%26gt%3B%22FT10%22%2CAF5%26lt%3B%26gt%3B%22FC10%22%2CAF5%26lt%3B%26gt%3B%22TXTX%22%2CAF5%26lt%3B%26gt%3B%22RSTK%22))%2C-S5%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831964%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401256%22%20target%3D%22_blank%22%3E%40cestrada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20simply%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(AND(OR(G5%3D%22DS%22%2CG5%3D%22DT%22)%2CAF5%26lt%3B%26gt%3B%22FT10%22%2CAF5%26lt%3B%26gt%3B%22FC10%22%2CAF5%26lt%3B%26gt%3B%22TXTX%22%2CAF5%26lt%3B%26gt%3B%22RSTK%22)%2C-S5%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831973%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401256%22%20target%3D%22_blank%22%3E%40cestrada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20the%20boolean%20logic.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DOR(G5%3D%22DS%22%2CG5%3D%22DT%22)*AND(AF5%26lt%3B%26gt%3B%22FT10%22%2CAF5%26lt%3B%26gt%3B%22FC10%22%2CAF5%26lt%3B%26gt%3B%22TXTX%22%2CAF5%26lt%3B%26gt%3B%22RSTK%22)*S5%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831976%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20needs%20only%20a%20small%20tweak...%3C%2FP%3E%3CP%3E%3DOR(G9%3D%22DS%22%2CG9%3D%22DT%22)*AND(AF9%26lt%3B%26gt%3B%22FT10%22%2CAF9%26lt%3B%26gt%3B%22FC10%22%2CAF9%26lt%3B%26gt%3B%22TXTX%22%2CAF9%26lt%3B%26gt%3B%22RSTK%22)*%3CSTRONG%3E-S9%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831977%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20thank%20you%20for%20the%20updated%20formula%2C%20that%20did%20the%20trick!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831978%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401256%22%20target%3D%22_blank%22%3E%40cestrada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20to%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832041%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20using%20IF%20and%20AND%20and%20OR%20and%20NOT%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401256%22%20target%3D%22_blank%22%3E%40cestrada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20use%20a%20table%20and%20group%20the%20Boolean%20conditions%20applied%20to%20a%20single%20variable%20within%20array%20constants%2C%20the%20resulting%20formula%20is%20almost%20readable%3C%2FP%3E%3CP%3E%3CFONT%3E%3D%20%3CSTRONG%3EIF%3C%2FSTRONG%3E(%20%3CSTRONG%3EAND%3C%2FSTRONG%3E(%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CSTRONG%3EOR%3C%2FSTRONG%3E(%20%3CFONT%20color%3D%22%230000ff%22%3E%5B%40%5BOrder%20Type%5D%5D%3C%2FFONT%3E%3D%3CFONT%20color%3D%22%23339966%22%3E%7B%22DS%22%2C%22DT%22%7D%3C%2FFONT%3E)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CSTRONG%3EAND%3C%2FSTRONG%3E(%20%3CFONT%20color%3D%22%230000ff%22%3E%5B%40Class%5D%3C%2FFONT%3E%26lt%3B%26gt%3B%3CFONT%20color%3D%22%23339966%22%3E%7B%22FT10%22%2C%22FC10%22%2C%22TXTX%22%2C%22RSTK%22%7D%3C%2FFONT%3E)%20)%2C%3CBR%20%2F%3E%26nbsp%3B%20-%3CFONT%20color%3D%22%230000ff%22%3E%5B%40%5BExt%20Price%5D%5D%3C%2FFONT%3E%2C%3CBR%20%2F%3E%26nbsp%3B%200%20)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
cestrada
New Contributor

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" or "FC10" or "TXTX" or "RSTK", then return the negative value in cell S5 otherwise, return 0.

 

Can anyone help with this?

 

thank you

 

11 Replies

@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

@Sergei Baklan 

 

Thank you so much for your help, it works perfectly!

@Sergei Baklan 

 

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.  

@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)

 

 

@cestrada 

Or simply this...

 

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

 

@cestrada 

Just the boolean logic.

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

 

@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 

 

Yes, thank you for the updated formula, that did the trick!

@cestrada 

You're welcome! Glad to help.

@cestrada 

If you use a table and group the Boolean conditions applied to a single variable within array constants, the resulting formula is almost readable

= IF( AND(
      OR( [@[Order Type]]={"DS","DT"} ),
      AND( [@Class]<>{"FT10","FC10","TXTX","RSTK"} ) ),
  -[@[Ext Price]],
  0 )

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies