asked on

MS Excel IF AND OR statement

I am having some challenges with IF AND OR conditional formulas in Excel. For example, say I am working a table, 5 columns wide, with field names CLASS, OBJ, DEF, BOTH? and TYPE. Items in class 1,2, or 3 have DEF value "fruit"; classes a, b, c, have DEF value of "veg" (the OBJ field just has placeholder values):

In the TYPE field, I am attempting to use an IF statement whereby if the class is a, b, or c, and BOTH? flag is "False", then the TYPE=A; if the class is 1,2, or 3 and the BOTH? flag is "FALSE", then the TYPE=B; If the class is a,b, or c or the class is 1, 2, or 3 and the BOTH? flag is "TRUE", then the TYPE is set to "BOTH."

My formula:
=IF(AND(A2="a",A2="b",A2"c", D2<>"TRUE"),"A",IF(AND(A2=1,A2=2,A2=3, D2<>"TRUE"),"B","BOTH"))
does not yield consistent results. I tried a number of variations but had no success.

Any ideas on how I can get this to work? The file is attached.

Thanks!

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mike in IT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wm Allen Smith

ASKER

Shums, Mike in IT,

Thank you so much for getting back to me so quickly.

Shums, your solution did not work for when BOTH? = TRUE; the type is listed as "A", which I had seen in some of my earlier attempts.
Mike in IT, your solution worked as expected, when BOTH?= TRUE, the Type is listed as BOTH.

Much appreciated!
wasmithpfs