• 515K Members
• 6,376 Online
• 611K Conversations

New Contributor

# 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" 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

# Re: Help with Excel using IF and AND and OR and NOT functions

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

Highlighted

# Re: Help with Excel using IF and AND and OR and NOT functions

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

# Re: Help with Excel using IF and AND and OR and NOT functions

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.

# Re: Help with Excel using IF and AND and OR and NOT functions

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)

# Re: Help with Excel using IF and AND and OR and NOT functions

Or simply this...

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

# Re: Help with Excel using IF and AND and OR and NOT functions

Just the boolean logic.

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

# Re: Help with Excel using IF and AND and OR and NOT functions

It needs only a small tweak...

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

# Re: Help with Excel using IF and AND and OR and NOT functions

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

# Re: Help with Excel using IF and AND and OR and NOT functions

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
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
Consolidating data from multiple worksheets
Machala Sentance in Excel on
3 Replies
Merge Cells with Different Data Types
CaffeineKing in Excel on
2 Replies