Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions
I have been trying for over an hour to get a formula to work. There are 4
specific items included in a column and 1 item in another column that if all of these meet the criteria, i want a "Y" returned. I am using If(AND(Bx="xxx")*and(bx="aaa")*and(bx="bbb")*and(b x="ccc")*and(px="r"),"Y","N")) and I am not getting the "Y" result when the data in bx has the required yes criteria. what am i doing wrong? I have tried many other ways and nothing is working |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions
Consider using COUNTIF()s and multiplying them.
=COUNTIF(A1:A100,"xxx")*COUNTIF(A1:A100,"bbb")*... .... if any condition fails, the COUNTIF() will be zero and the product will also be zero. If 0 then no else yes. -- Gary''s Student - gsnu200901 "Terri Farm" wrote: I have been trying for over an hour to get a formula to work. There are 4 specific items included in a column and 1 item in another column that if all of these meet the criteria, i want a "Y" returned. I am using If(AND(Bx="xxx")*and(bx="aaa")*and(bx="bbb")*and(b x="ccc")*and(px="r"),"Y","N")) and I am not getting the "Y" result when the data in bx has the required yes criteria. what am i doing wrong? I have tried many other ways and nothing is working |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions
If(AND(Bx="xxx")*and(bx="aaa")*and(bx="bbb")*and( bx="ccc")*and(px="r"),"Y","N"))
If you remove the last ")" and replace the "x" with a valid row number your formula is logically correct. However, you might want to try it like this: =IF(AND(B1="xxx",B2="aaa",B3="bbb",B4="ccc",P1="r" ),"Y","N") Use the correct row numbers in the cell references. -- Biff Microsoft Excel MVP "Terri Farm" wrote in message ... I have been trying for over an hour to get a formula to work. There are 4 specific items included in a column and 1 item in another column that if all of these meet the criteria, i want a "Y" returned. I am using If(AND(Bx="xxx")*and(bx="aaa")*and(bx="bbb")*and(b x="ccc")*and(px="r"),"Y","N")) and I am not getting the "Y" result when the data in bx has the required yes criteria. what am i doing wrong? I have tried many other ways and nothing is working |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions
Showing us your non-working formula is always a good idea, but you need to
couple that with a *full* explanation of what you expected that formula to do. Showing actual examples (using real cell addresses as opposed to using and 'x' as the row number) is also a good idea. The problem I am having with trying to figure out what your formula is supposed to be doing is it looks like you want the same cell (Bx) to be equal to "xxx", "aaa", etc. all at the same time... that doesn't make sense to me. -- Rick (MVP - Excel) "Terri Farm" wrote in message ... I have been trying for over an hour to get a formula to work. There are 4 specific items included in a column and 1 item in another column that if all of these meet the criteria, i want a "Y" returned. I am using If(AND(Bx="xxx")*and(bx="aaa")*and(bx="bbb")*and(b x="ccc")*and(px="r"),"Y","N")) and I am not getting the "Y" result when the data in bx has the required yes criteria. what am i doing wrong? I have tried many other ways and nothing is working |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions
It didn't work. the "bbb" is text not numbers. does this matter?
"Gary''s Student" wrote: Consider using COUNTIF()s and multiplying them. =COUNTIF(A1:A100,"xxx")*COUNTIF(A1:A100,"bbb")*... .... if any condition fails, the COUNTIF() will be zero and the product will also be zero. If 0 then no else yes. -- Gary''s Student - gsnu200901 "Terri Farm" wrote: I have been trying for over an hour to get a formula to work. There are 4 specific items included in a column and 1 item in another column that if all of these meet the criteria, i want a "Y" returned. I am using If(AND(Bx="xxx")*and(bx="aaa")*and(bx="bbb")*and(b x="ccc")*and(px="r"),"Y","N")) and I am not getting the "Y" result when the data in bx has the required yes criteria. what am i doing wrong? I have tried many other ways and nothing is working |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions
OK. I started off in row 2 with the formula and just copy it down to the
bottom of my data. What I am trying to accomplish is get a "Y" returned when the data in column b matches my criteria and also matches my criteria in column p. If the data in column b equals "57C", "77C","157c","858C" and the data in column p equals "r" for that specific row, i want a "y" to be returned. I am getting an "n". If I take out all of the different criteria for column b specified in my formula except the one that actually matches what's there, I am getting the desired result of "y". "Rick Rothstein" wrote: Showing us your non-working formula is always a good idea, but you need to couple that with a *full* explanation of what you expected that formula to do. Showing actual examples (using real cell addresses as opposed to using and 'x' as the row number) is also a good idea. The problem I am having with trying to figure out what your formula is supposed to be doing is it looks like you want the same cell (Bx) to be equal to "xxx", "aaa", etc. all at the same time... that doesn't make sense to me. -- Rick (MVP - Excel) "Terri Farm" wrote in message ... I have been trying for over an hour to get a formula to work. There are 4 specific items included in a column and 1 item in another column that if all of these meet the criteria, i want a "Y" returned. I am using If(AND(Bx="xxx")*and(bx="aaa")*and(bx="bbb")*and(b x="ccc")*and(px="r"),"Y","N")) and I am not getting the "Y" result when the data in bx has the required yes criteria. what am i doing wrong? I have tried many other ways and nothing is working |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions
Still not worded as clear as I would like, but let me take a guess. When you
say... data in column b equals "57C", "77C","157c","858C" do you mean the value in the cell in column b equals *either* "57C", "77C","157c" *or* "858C"? If yes, then try this formula in row 2 and copy it down as needed... =IF(AND(OR(B2={"57C","77C","157c","858C"}),P2="r") ,"Y","N") -- Rick (MVP - Excel) "Terri Farm" wrote in message ... OK. I started off in row 2 with the formula and just copy it down to the bottom of my data. What I am trying to accomplish is get a "Y" returned when the data in column b matches my criteria and also matches my criteria in column p. If the data in column b equals "57C", "77C","157c","858C" and the data in column p equals "r" for that specific row, i want a "y" to be returned. I am getting an "n". If I take out all of the different criteria for column b specified in my formula except the one that actually matches what's there, I am getting the desired result of "y". "Rick Rothstein" wrote: Showing us your non-working formula is always a good idea, but you need to couple that with a *full* explanation of what you expected that formula to do. Showing actual examples (using real cell addresses as opposed to using and 'x' as the row number) is also a good idea. The problem I am having with trying to figure out what your formula is supposed to be doing is it looks like you want the same cell (Bx) to be equal to "xxx", "aaa", etc. all at the same time... that doesn't make sense to me. -- Rick (MVP - Excel) "Terri Farm" wrote in message ... I have been trying for over an hour to get a formula to work. There are 4 specific items included in a column and 1 item in another column that if all of these meet the criteria, i want a "Y" returned. I am using If(AND(Bx="xxx")*and(bx="aaa")*and(bx="bbb")*and(b x="ccc")*and(px="r"),"Y","N")) and I am not getting the "Y" result when the data in bx has the required yes criteria. what am i doing wrong? I have tried many other ways and nothing is working |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logical Functions | Excel Discussion (Misc queries) | |||
Logical Functions | Excel Worksheet Functions | |||
Logical Functions | Excel Worksheet Functions | |||
logical functions | Excel Worksheet Functions | |||
logical functions | Excel Worksheet Functions |