Excel Nested If Or Statements, returning FALSE or #VALUE

[WildbloodS]

Hi, any help most welcome with correcting a formula where I am trying to evaluate several codes in Column G

Depending on what code exists in column G I would like to check if either column I or Column J contains data.

If data exists based on those criteria, I would like in the target cell to return "Passed Check 1", or, "Failed Check 1".

However:

With this code I get the return FALSE
=IF(OR(G4="JZ110RTK0001",G4="JZ190RTK0001"),IF(COUNTBLANK(J4),IF(OR(G4="JZ135RTK0001"),IF(COUNTBLANK(I4),"Failed Check 1","Passed Check 1"))))


With this code I get the return #VALUE
=IF(OR(G4="JZ110RTK0001",G4="JZ190RTK0001"),IF(COUNTBLANK(J4),"Failed Check 1","Passed Check 1"),"Not Applicable"),IF(OR(G4="JZ135RTK0001"),IF(COUNTBLANK(I4),"Failed Check 1","Passed Check 1"))

- I would normally use Access to do all this quite comfortably, however, I've been asked to put this together quickly using Excel.

Thank you

 

[SkipVought]

HI,

It would help if you would post an example with actual values and also post the expected results. You could alternatively upload a workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ajh1]

If you expand out your first example you get the following:
IF(OR(G4="JZ110RTK0001",G4="JZ190RTK0001") then
IF(COUNTBLANK(J4)then
IF(OR(G4="JZ135RTK0001") then
IF(COUNTBLANK(I4)then
"Failed Check 1",
else
"Passed Check 1"
endif)
endif)
endif)
endif)

(Note in this case you only get an output if G4 is BOTH either "JZ110RTK0001" or "JZ190RTK0001" AND "JZ135RTK001" which is clearly impossible, hence the FALSE reading.

For your second example you get the following:
IF(OR(G4="JZ110RTK0001",G4="JZ190RTK0001")then
IF(COUNTBLANK(J4)then
"Failed Check 1"
else
"Passed Check 1"
endif)
else
"Not Applicable")
endif
IF(OR(G4="JZ135RTK0001") then
IF(COUNTBLANK(I4) then
"Failed Check 1"
else
"Passed Check 1"
endif)
endif)

This would make somewhat more sense, except you can't stack if statements quite like this in a single statement so the whole second block is not permitted, hence the #VALUE result.
=IF(OR(G4="JZ110RTK0001",G4="JZ190RTK0001"),IF(COUNTBLANK(J4),"Failed Check 1","Passed Check 1"),"Not Applicable"),IF(OR(G4="JZ135RTK0001"),IF(COUNTBLANK(I4),"Failed Check 1","Passed Check 1"))

 

[SkipVought]

Again, in order to be able to test a solution, you ought to have the courtesy to either
1) post a test set so that members can COPY your test data and PASTE it into a sheet or
2) upload a workbook containing your test set.

It would also be helpful and courteous to post the results your unexpected with each row of test data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[WildbloodS]

Thank you, I have created a work around using an additional calculated column, due to pressures of time. Thanks again.