Try Converting this formula

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

Can anyone give me the Excel equivalent to this formula that both and AND condition and an OR condition embedded in there. I can do an AND, but I can't seem to figure out the right syntax for both an AND and an OR condition



@IF(@INDEX(Data,17,ROW)=0#AND#@INDEX(Data,5,ROW)<>"6"#OR#"7","0","1")
 
This is a multi-part message in MIME format.
Click to expand...
...

Don't post in MIME/HTML. Post in *PLAIN* text.
@IF(@INDEX(Data,17,ROW)=3D0#AND#@INDEX(Data,5,ROW)<>"6"#OR#"7","0","1")
Click to expand...

This is an example of the rubbish that MIME adds to your text - the '=3D0'
rahter than '=0'.

@IF(@INDEX(Data,17,ROW)=0#AND#@INDEX(Data,5,ROW)<>"6"#OR#"7","0","1")

This is bad style generally, and it's gibberish in this example formula. AND and
OR operators and their operands should always be enclosed in parentheses in
order to make the order of evaluation clear.

For this particular formula, 123 and Quattro Pro would evaluate it as

@IF( (@INDEX(Data,17,ROW)=0#AND#@INDEX(Data,5,ROW)<>"6")#OR#"7", "0","1")

Since "7" is text, it's always converted to FALSE in boolean expressions, so the
formula could be simplified by eliminating it.

On the other hand, the set of all values for which @INDEX(Data,5,ROW)<>"6" is
true contains "7", and the set of all values for which @INDEX(Data,5,ROW)<>"7"
contains "6", so if you mean

@INDEX(Data,5,ROW)<>"6"#OR#@INDEX(Data,5,ROW)<>"7"

it's *ALWAYS* true, so serves no purpose as part of a longer boolean expression.

However, I think you mean @INDEX(Data,5,ROW) should be neither "6" nor "7". If
so, it's equivalent to

@INDEX(Data,5,ROW)<>"6"#AND#@INDEX(Data,5,ROW)<>"7"

so no OR operation involved. If so, one possible Excel formula would be

=IF(AND(INDEX(Data,ROW+1,17+1)=0,INDEX(Data,ROW+1,5+1)<>"6",
INDEX(Data,ROW+1,5+1)<>"7","0","1")
 
Back
Top