Multiple AND & OR statements

J

jamie.cutting

The Formula:

=IF(AND(ISNUMBER(SEARCH({"PRO","NOP","VAL"},B55)),OR(H55>524,AND(I55>1568,I55<3238),AND(J55>4883,
J55<9077),AND(K55>861,K55<1674))),10,0)


I am trying to achieve the following logic:

If B55 includes PRO NOP or VAL and either H55>524, 1568<I55<3238,
4883<J55<9077 or 861<K55<1674 then return the value 10, if not return
0. But the above formula always appears to return 0 and I was unseure
about the multiple AND & OR statements.

Any help would be gratefully recieved.

Regards

Jamie
 
J

joeu2004

The Formula:
=IF(AND(ISNUMBER(SEARCH({"PRO","NOP","VAL"},B55)),OR(H55>524,AND(I55>1568,I­55<3238),AND(J55>4883,
J55<9077),AND(K55>861,K55<1674))),10,0)

I am trying to achieve the following logic:
If B55 includes PRO NOP or VAL and either H55>524, 1568<I55<3238,
4883<J55<9077 or 861<K55<1674 then return the value 10, if not return
0. But the above formula always appears to return 0 and I was unseure
about the multiple AND & OR statements.
Click to expand...

Using Tools -> Formula Auditing -> Evaluate Formula reveals the root
cause of the problem: {"PRO","NOP","VAL"} always evaluates to "PRO".
So the formula should return 10 when B55 contains "PRO" and one of the
other conditions is met. Off-hand, the compound OR-AND expression
looks okay to me.

The following change should work, entered as an array formula (ctrl-
shift-Enter):

=IF(AND(or(ISNUMBER(SEARCH({"PRO","NOP","VAL"},B55))),OR(...)),10,0)

In other words, an OR() function is wrapped around the ISNUMBER()
function.

But there might be a better solution that does not require an array
formula.
 
J

joeu2004

[Repost after transmission error]

The Formula:
=IF(AND(ISNUMBER(SEARCH({"PRO","NOP","VAL"},B55)),OR(H55>524,AND(I55>1568,I­55<3238),AND(J55>4883,
J55<9077),AND(K55>861,K55<1674))),10,0)

I am trying to achieve the following logic:
If B55 includes PRO NOP or VAL and either H55>524, 1568<I55<3238,
4883<J55<9077 or 861<K55<1674 then return the value 10, if not return
0. But the above formula always appears to return 0 and I was unseure
about the multiple AND & OR statements.
Click to expand...

Using Tools -> Formula Auditing -> Evaluate Formula reveals the root
cause of the problem: {"PRO","NOP","VAL"} always evaluates to "PRO".
So the formula should return 10 when B55 contains "PRO" and one of the
other conditions is met. Off-hand, the compound OR-AND expression
looks okay to me.

The following change should work, entered as an array formula (ctrl-
shift-Enter):

=IF(AND(or(ISNUMBER(SEARCH({"PRO","NOP","VAL"},B55))),OR(...)),10,0)

In other words, an OR() function is wrapped around the ISNUMBER()
function.

But there might be a better solution that does not require an array
formula.
 
M

macropod

Hi Jamie,

Try:
=AND(OR(ISNUMBER(SEARCH("PRO",B5)),ISNUMBER(SEARCH("NOP",B5)),ISNUMBER(SEARCH("VAL",B5))),OR(H5>524,AND(I5>1568,I5<3238),AND(J5>4883,J5<9077),AND(K5>861,K5<1674)))*10

Cheers
 
S

Sebation.G

TRY:
=IF(AND(OR(ISNUMBER(SEARCH({"PRO","NOP","VAL"},B55)),OR(H55>524,AND(I55>1568,I55<3238),AND(J55>4883,J55<9077),AND(K55>861,K55<1674)))),10,0)
 
S

Sebation.G

=IF(AND(OR(ISNUMBER(SEARCH({"PRO","NOP","VAL"},B55))),OR(H55>524,AND(I55>1568,I55<3238),AND(J55>4883,J55<9077),AND(K55>861,K55<1674))),10,0)

pre-post lost something i renew it here
 
J

joeu2004

Errata....

The following change should work, entered as an array formula (ctrl-
shift-Enter):
=IF(AND(or(ISNUMBER(SEARCH({"PRO","NOP","VAL"},B55))),OR(...)),10,0)
[....]
But there might be a better solution that does not require an array
formula.
Click to expand...

As Sebation.G's posting demonstrates, this does not have to be entered
as an array formula after all. Great!
 
Top