Aug 30 2019 12:41 PM
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
Aug 30 2019 12:58 PM
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
Aug 30 2019 01:30 PM
Aug 30 2019 02:06 PM
@cestrada , you are welcome
Aug 30 2019 02:11 PM
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.
Aug 30 2019 02:32 PM
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)
Aug 30 2019 02:34 PM
Or simply this...
=IF(AND(OR(G5="DS",G5="DT"),AF5<>"FT10",AF5<>"FC10",AF5<>"TXTX",AF5<>"RSTK"),-S5,0)
Aug 30 2019 02:42 PM
Just the boolean logic.
=OR(G5="DS",G5="DT")*AND(AF5<>"FT10",AF5<>"FC10",AF5<>"TXTX",AF5<>"RSTK")*S5
Aug 30 2019 02:45 PM
It needs only a small tweak...
=OR(G9="DS",G9="DT")*AND(AF9<>"FT10",AF9<>"FC10",AF9<>"TXTX",AF9<>"RSTK")*-S9
Aug 30 2019 02:47 PM
Aug 30 2019 02:48 PM
You're welcome! Glad to help.
Aug 30 2019 04:07 PM
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 )