Nested Logic Statements with multiple logic

[Jerry]

Hi,

I'm having a problem with a logic statement. Here is one
statement that I've already done that works.

=IF(AND(OR(OR(B5>=19,C5>=19),OR(E5>=19,F5>=19)),OR
(H2>=19,I2>=19)),1,0)

Now, I want to include some added conditions to the above
function that is already working correctly. In english,
what I want to do is this....

If ((b5>=19 or c5 >=19 OR d5>=19 or e5 >=19) AND f5>=0.2)
AND ((g5>=19 or h5>=19) and i5>=0.2) then return a value
of 1, otherwise, return a zero.

The formula that I've been using for this is below:

=IF(AND(AND(AND(G2>=0.2,OR(E2>=19,F2>=19),1,0),OR
(B2>=19,C2>=19),1,0),D2>=0.2),1,0)


I know the cell references do not match my english
explanation, but that's O.K.
I'm getting a "You've entered too many arguments for this
function" statement. Can anybody help with this? Thank
you.

 

[Alan Beban]

I didn't thoroughly test it, but I believe this is it:

=IF(AND(OR(B5>=19,C5>=19,D5>=19,E5>=19),F5>=0.2,OR(G5>=19,H5>=19),I5>=0.2),1,0)

Alan Beban

 

[Tim C]

Just to complicate it, as long as F5 and I5 have numbers, this should work:

=(MIN(MAX(B5:E5),MAX(G5:H5))>=19)*(MIN(F5,I5)>=0.2)

Tim C

 

[Jerry]

I just want to thank everybody for helping out with this
formula. Alan, I've tested out your formula and it works
great! I'll have to still research the other ones and
see how they work. Thanks again.

 

[Random]

Actually, I don't think they do. They appear to be the same, except
your formula is a little cleaner than mine :{.

The only real difference between the two is that mine reads like the
request was written (easier to understand for newbies like myself).

All in all though, I would use yours because it did not use the
unnecessary nesting that mine did.

Random

 

[Ron Rosenfeld]

If ((b5>=19 or c5 >=19 OR d5>=19 or e5 >=19) AND f5>=0.2)
AND ((g5>=19 or h5>=19) and i5>=0.2) then return a value
of 1, otherwise, return a zero.

Just to show you a somewhat different approach that will yield the same result,
and also give you an exposure to array formulas, the array-entered formula:

=--AND(OR(B5:E5>=19),OR(G5:H5>=19),AND(F5>=0.2,I5>=0.2))

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula.

Your logic seems to be equivalent to saying
B5, C5, D5 OR E5 >=19
AND
G5 OR H5 >=19
AND
F5 AND I5 >= 0.2

============


--ron

 

[Jerry]

Sorry Allen. I didn't change the cells to match my
spreadsheet. Yours works as well. I apologize for you
going on a wild goose chase when it worked all along.
That shows you my competence in Excel right now! I
have another challenge now, though. Here's the extra
challenge. If (((b5 >= 19 or c5 >= 19)AND D5 >= 0.2) OR
((e5>=19 OR f5 >=19) AND g5>=0.2) AND ((H5 >=19 OR
I5>=19) AND J5>=19))
This makes it a bit tougher because b5, c5, e5, and f5
can't rely on the same cell to see if the value is above
0.2 or not. Therefore, the logic gets more involved.
If the statement above is true, then return a value of 1,
otherwise, return a 0. Any suggestions? I've tried to
make this work, but I'm not getting anywhere. Frustrated
is a good word to describe my day yesterday trying to
figure it out! Again, thanks for everybody's time and
effort.

You'll noticed I used some values besides the 19 I put in
the above request. I did the 19's in the request to make
it easier on whoever works on the formula.

 

[Random]

If you mean that no matter what, J5 must be greater than 19 for the
formula to return 1 then this is what I think you are looking for.

=IF(AND(OR(AND(OR(B5>=19, C5>=19),D5>=0.2), AND(OR(E5 >= 19, F5>= 19),
G5>=0.2)), OR(H5>=19, I5>=19), J5>=19),1,0)


Random

Sorry Allen. I didn't change the cells to match my
spreadsheet. Yours works as well. I apologize for you
going on a wild goose chase when it worked all along.
That shows you my competence in Excel right now! I
have another challenge now, though. Here's the extra
challenge.

If (((b5 >= 19 or c5 >= 19)AND D5 >= 0.2)
OR
((e5>=19 OR f5 >=19) AND g5>=0.2)
AND
((H5 >=19 OR >I5>=19) AND J5>=19))

 

[Jerry]

Random,

I should have had J5 >- 0.2, not 19. Regardless, I'm
going to put this in the spreadsheet and tell you if it
works. Thanks again!

 

[Jerry]

Random,

It works! Thank you. I still need to see exactly HOW it
works. My original formula that I was fumbling around
with looked like this.

=IF(AND(OR(B5>19,C5>19),D5>0.2),AND(OR
(E5>19,F5<19),G5>0.2),OR(H5>19),I5>19),J5>0.2),1,0)
I do see now that the extra AND(OR was needed at the
beginning because the , after the D5,0.2) needed to be
defined as an OR. The extra AND at the beginning defined
the , after the G5>0.2)).