Nested IF function Revisted

[Guest]

I asked this a few days ago but I didn't realize I had other factors I needed
to account for.
I need to modify the formula I am using:
=IF(OR(J2="N",K2="N",M2="N"),"N",(IF(AND(J2="SP*",K2="Y",M2="Y"),"Y","")))

I'm trying to create a function to automatically determine if an engine is
fully
assembled in column N.

If the answer is "N" (no) to either columns J, K or M then column N
must display "N" (no, the engine is not completely assembled). If the answer
is "Y" (yes) to columns K and M and if column J has an SP # in it (i.e.
SP123-12) then column N must display "Y". If all columns J thru M are left
blank then column "N" must display nothing (blank). (By the way, I'm not
including the "L" column on purpose).
I've been getting "N"s on the rows I want to get "N"s but blanks on all rows
I should be getting "Y"s for.

J K L M N
Belt Motor Oil Valves Engine Assembled?
1
2 N N Y N N (=IF function)
3 SP123-12 Y Y Y Y (=IF function)

Help! What am I doing wrong?

Thank you,

Studebaker

 

[T. Valko]

Try this:

=IF(OR(J2="N",K2="N",M2="N"),"N",IF(AND(LEFT(J2,2)="SP",K2="Y",M2="Y"),"Y",""))

Or:

=IF(COUNTIF(J2:M2,"N"),"N",IF(AND(LEFT(J2,2)="SP",K2="Y",M2="Y"),"Y",""))

Biff

 

[David Biddulph]

It's your J2="SP*"
Try
=IF(OR(J2="N",K2="N",M2="N"),"N",(IF(AND(FIND("SP",J2)=1,K2="Y",M2="Y"),"Y","")))

You still haven't specified what you want if you've got a mixture of blanks
& Y (or SP) responses. At the moment your formula gives a blank as the
result.

 

[T. Valko]

.....(IF(AND(FIND("SP",J2)=1....

That will cause an error if "SP" is not found.

Biff

 

[Guest]

On your second formula is not alway TRUE

what if J2 =Y, K2=Y,M2=Y, and L2=N (the OP wants J2,K2,M2 not L2)

 

[Guest]

the way i see it is that u need a prioritize mark result in column N
"Y" (yes), "" (Blank) , "N" (no).


=IF(AND(LEFT(J2,2)="SP",K2="Y",M2="Y"),"Y",IF(AND(J2="",K2="",M2=""),"","N"))

 

[T. Valko]

Did you notice which formula I suggested first?

Biff

 

[Guest]

Hi David,

Thank you. You've been very helpful. I'm going to try this out.
The Y or blank scenario will never happen so that's why I never mentioned
it. The rows will either be filled in or left completey blank.

Thanks again. Hopefully this will do the trick.

Studebaker