More help with nested IF OR AND

[HeyInKy]

Here was my 1st attempt:

http://www.excelforum.com/excel-form...48#post3214148

with the solution of =IF(AND(I7=9,I15<>1),"",IF(I17<9,"Invalid",IF(OR(AND(I17>10,I17<28),I15=1),"Error","")))

This is quite helpful, but after playing with it some more, not quite want I want (but close).

Here is what I want:

IF I17=9 AND I15<>1 then return blank " "

IF I17=9 AND I15=1 then return "Error"

IF I17<9 OR Between the ranges of 11 to 18 (>=11 thru <=18) return "Invalid" regardless of I15

IF I17=19 AND I15 <>1 then return blank " "

IF I17=19 AND I15=1 then return "Error"

Here's my attempt which is close but not quite:
=IF(AND(I17=9,I15<>1),"",IF(OR(IF(AND(I17<9,I17=11,)*I17>=12,I17<=18)),"INVALID",IF(OR(I17=19,I15=1),"ERROR","")))

In summary,basically, if I17 is 9 or 19 and if I15 is not 1, then everything is fine (return blank), but if I17 is 9 or 19 and I15 is 1, return "error", but if I17 is anything other than 9 or 19 and no matter what I15 is, then return "invalid"

The problem I seem to be having is specifying the range if I17 is between 11-18... regardless if I15 is a 1 or not.


...If I could get this string down, then I may just add 1 more complexity, and try to get "Error" whenever I15=1 OR I16=1 when I17 is 9 or 19.

[Special-K]

Try

=IF(OR(I19<9,AND(I17>=11,I17<=18)),"Invalid",IF(OR(AND(I17=9,I15<>1),AND(I17=19,I15<>1)),"",IF(OR(AND(I17=9,I15=1),AND(I17=19,I15=1)),"Error")))

[HeyInKy]

Yepp - that works!! Your a nesting genius!! (Only small error was the very first cell reference is I17 not I19, but nbd). I will mark this thread solved before the end of the day and add reputation - but for extra credit, where would the OR statement go to include either I15 OR if I16 = 1 (or <>1) to return "error" (or "" if using <>)?