We help IT Professionals succeed at work.

Is this question possible in an excel formula

if e# = No
and if d# <> Yes-L
or f# = No
AND
h# or i# or j# = yes
then set column = yes
sampleexp.xlsx
Comment
Watch Question

BRONZE EXPERT

Commented:
=IF(OR(AND(E2 = "No", D2 <> "Yes-L"),AND(F2 = "No",OR(H2="yes", I2="yes", J2="yes"))),"yes","")
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
=IF(AND(E2="no",OR(D2<>"Yes-L",F2="No"),OR(H2:J2="Yes")),"Yes","")
Matt PinkstonEnterprise Architect

Author

Commented:
seems like I am getting more than I should

I think your formula is doing

if e# = No
and if d# <> Yes-L
or f# = No

OR  instead of AND

h# or i# or j# = yes
then set column = yes
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
It would be helpful to have a sample workbook with test data and expected results. Here is mine:  Complex IF formula.xlsx
Matt PinkstonEnterprise Architect

Author

Commented:
sample attached
sampleexp.xlsx
Matt PinkstonEnterprise Architect

Author

Commented:
byundt
wrong and or nesting and gets error
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I reproduce the pink values with:
=IF(AND(OR(AND(E2="no",D2<>"Yes-L"),F2="No"),OR(H2:J2="Yes")),"Yes","")
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Matt PinkstonEnterprise Architect

Author

Commented:
the attached formula return #value!
Matt PinkstonEnterprise Architect

Author

Commented:
if e# = No
and if d# <> Yes-L
or f# = No

AND only if at least one of the following h#,I#,j# is yes
Matt PinkstonEnterprise Architect

Author

Commented:
if (e# = No and if d# <> Yes-L) or f# = No

AND only if at least one of the following h#,I#,j# is yes
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Matt,
In Office 365, my suggested formula does not need array-entry. In older versions of Excel, it needs to be array-entered (Control + Shift + Enter. I posted a sample workbook so you could see the formula working.

Brad
Matt PinkstonEnterprise Architect

Author

Commented:
you lost me
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
  1. Select the cell
  2. Click in the formula bar
  3. Hold the Control and Shift keys down, then hit Enter and release all three keys
  4. Excel should respond by changing from #VALUE! error to a sensible answer. Excel will also surround the formula with curly braces { }.

If you still see #VALUE!, then repeat the procedure.
Mechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you want a regular formula (does not require array-entry), you may use the somewhat longer:
=IF(AND(OR(AND(E2="no",D2<>"Yes-L"),F2="No"),OR(H2="Yes",I2="Yes",J2="Yes")),"Yes","")
sampleexp.xlsx
Matt PinkstonEnterprise Architect

Author

Commented:
Thanks