We help IT Professionals succeed at work.

# Is this question possible in an excel formula

on
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

## View Solution Only

BRONZE EXPERT

Commented:
=IF(OR(AND(E2 = "No", D2 <> "Yes-L"),AND(F2 = "No",OR(H2="yes", I2="yes", J2="yes"))),"yes","")
Mechanical 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","")`
Enterprise Architect

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

h# or i# or j# = yes
then set column = yes
Mechanical 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
Enterprise Architect

Commented:
sample attached
sampleexp.xlsx
Enterprise Architect

Commented:
byundt
wrong and or nesting and gets error
Mechanical 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","")`
Mechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Enterprise Architect

Commented:
the attached formula return #value!
Enterprise Architect

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
Enterprise Architect

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
Mechanical 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.

Enterprise Architect

Commented:
you lost me
Mechanical 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
Enterprise Architect

Commented:
Thanks