| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested Function | Excel Discussion (Misc queries) | |||
| Nested IF function | Excel Worksheet Functions | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
| Countif Function -Nested | Excel Discussion (Misc queries) | |||
Linear Mode
