Nested IF function Revisted
 

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

Nested IF function Revisted
 

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

"Studebaker" wrote in message
...
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

Nested IF function Revisted
 

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.
--
David Biddulph

"Studebaker" wrote in message
...
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

Nested IF function Revisted
 

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

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

Biff

"David Biddulph" wrote in message
...
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.
--
David Biddulph

"Studebaker" wrote in message
...
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

Nested IF function Revisted
 

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)


"T. Valko" wrote:

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

"Studebaker" wrote in message
...
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

Nested IF function Revisted
 

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"))
--
*****
birds of the same feather flock together..



"Studebaker" wrote:

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

Nested IF function Revisted
 

Did you notice which formula I suggested first?

Biff

"Teethless mama" wrote in message
...
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)


"T. Valko" wrote:

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

"Studebaker" wrote in message
...
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

Nested IF function Revisted
 

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

"David Biddulph" wrote:

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.
--
David Biddulph

"Studebaker" wrote in message
...
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