| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND functions
Hi, I'm trying to create an answer using a combination of "and" and "if"
statements for a number of cells - without success. I've done the following formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2 ). Can anyone help me with what I'm missing to make the formula look for the "true" or "false" result across the 4 cells? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND functions
=IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q 2)
You don't want to quote numbers: "0" Try it like this: =IF(AND(AA2="p",Q2=0,U2=0,T2="nil"),0,Q2) -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... Hi, I'm trying to create an answer using a combination of "and" and "if" statements for a number of cells - without success. I've done the following formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2 ). Can anyone help me with what I'm missing to make the formula look for the "true" or "false" result across the 4 cells? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND functions
Hi Biff, I think maybe my assumptions of true vs false may be the problem.
Column Q is either value or 0, column T is either nil or something else (alpha numeric combinations) row U is 0 - 4. If there is value - more than 0 in Q and either nil or 0 in T or U columns I want it to to give me the value of Q. Currently with the below formulas it gives me the value of Q whether U or T meet my true criteria or not. "T. Valko" wrote: =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q 2) You don't want to quote numbers: "0" Try it like this: =IF(AND(AA2="p",Q2=0,U2=0,T2="nil"),0,Q2) -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... Hi, I'm trying to create an answer using a combination of "and" and "if" statements for a number of cells - without success. I've done the following formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2 ). Can anyone help me with what I'm missing to make the formula look for the "true" or "false" result across the 4 cells? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND functions
I'm confused!
Is nil slang for an empty cell or do you mean the literal word nil? -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... Hi Biff, I think maybe my assumptions of true vs false may be the problem. Column Q is either value or 0, column T is either nil or something else (alpha numeric combinations) row U is 0 - 4. If there is value - more than 0 in Q and either nil or 0 in T or U columns I want it to to give me the value of Q. Currently with the below formulas it gives me the value of Q whether U or T meet my true criteria or not. "T. Valko" wrote: =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q 2) You don't want to quote numbers: "0" Try it like this: =IF(AND(AA2="p",Q2=0,U2=0,T2="nil"),0,Q2) -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... Hi, I'm trying to create an answer using a combination of "and" and "if" statements for a number of cells - without success. I've done the following formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2 ). Can anyone help me with what I'm missing to make the formula look for the "true" or "false" result across the 4 cells? Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND functions
'nil' is the literal word
"T. Valko" wrote: I'm confused! Is nil slang for an empty cell or do you mean the literal word nil? -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... Hi Biff, I think maybe my assumptions of true vs false may be the problem. Column Q is either value or 0, column T is either nil or something else (alpha numeric combinations) row U is 0 - 4. If there is value - more than 0 in Q and either nil or 0 in T or U columns I want it to to give me the value of Q. Currently with the below formulas it gives me the value of Q whether U or T meet my true criteria or not. "T. Valko" wrote: =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q 2) You don't want to quote numbers: "0" Try it like this: =IF(AND(AA2="p",Q2=0,U2=0,T2="nil"),0,Q2) -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... Hi, I'm trying to create an answer using a combination of "and" and "if" statements for a number of cells - without success. I've done the following formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2 ). Can anyone help me with what I'm missing to make the formula look for the "true" or "false" result across the 4 cells? Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND functions
If there is value - more than 0 in Q and
either nil or 0 in T or U columns I want it to to give me the value of Q. Ok, that sounds like an OR condition for the second line: AND Q0, OR T="nil", U=0 Try this: =IF(AND(AA2="p",N(Q2)0,OR(U2=0,T2="nil")),Q2,0) If U2 is an empty cell it will evaluate as 0. Is that something you'd need to account for? -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... 'nil' is the literal word "T. Valko" wrote: I'm confused! Is nil slang for an empty cell or do you mean the literal word nil? -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... Hi Biff, I think maybe my assumptions of true vs false may be the problem. Column Q is either value or 0, column T is either nil or something else (alpha numeric combinations) row U is 0 - 4. If there is value - more than 0 in Q and either nil or 0 in T or U columns I want it to to give me the value of Q. Currently with the below formulas it gives me the value of Q whether U or T meet my true criteria or not. "T. Valko" wrote: =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q 2) You don't want to quote numbers: "0" Try it like this: =IF(AND(AA2="p",Q2=0,U2=0,T2="nil"),0,Q2) -- Biff Microsoft Excel MVP "Mary-Anne" wrote in message ... Hi, I'm trying to create an answer using a combination of "and" and "if" statements for a number of cells - without success. I've done the following formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2 ). Can anyone help me with what I'm missing to make the formula look for the "true" or "false" result across the 4 cells? Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND functions
Your formula =IF(AND(AA2="p",Q2="0",U2="0",T2="nil"),0,Q2 ) will return 0 if true and the contents of Q2, is this what you want?Mary-Anne;176953 Wrote: Hi, I'm trying to create an answer using a combination of "and" and "if" statements for a number of cells - without success. I've done the following formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2 ). Can anyone help me with what I'm missing to make the formula look for the "true" or "false" result across the 4 cells? Thanks -- The Code Cage Team Regards, The Code Cage Team 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48951 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND functions
yes the problem is that even if T2 and U2 are not equal to 0 - it is giving
me the value of Q2 as the answer "The Code Cage Team" wrote: Your formula =IF(AND(AA2="p",Q2="0",U2="0",T2="nil"),0,Q2 ) will return 0 if true and the contents of Q2, is this what you want?Mary-Anne;176953 Wrote: Hi, I'm trying to create an answer using a combination of "and" and "if" statements for a number of cells - without success. I've done the following formula =IF(AND(AA2="p",(Q2="0"),(U2="0"),(T2="nil")),0,Q2 ). Can anyone help me with what I'm missing to make the formula look for the "true" or "false" result across the 4 cells? Thanks -- The Code Cage Team Regards, The Code Cage Team 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48951 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple functions, conditional functions | Excel Worksheet Functions | |||
| How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
| efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
| Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
| Nesting functions in the functions dialog box | Excel Worksheet Functions | |||
Hybrid Mode
