AND with multiple conditions

[Guest]

I'm trying to create a formula to test multiple situations. I have a mock up
of my spreadsheet below:

Col F: data i'm testing
Col G: Test One--I have a nested IF formula here that turns out either a
"TRUE" or "FALSE".
Col H: Test Two--I want to build a conditional formula to test that if the
test in Col. G results in "FALSE" then it references the data in Col. F and
for all items "dog" "cat" "mouse" in the same row(from Col. F) then it
returns with "pet". If the data in Col. G is "FALSE" but the data in the
same row in Col. F is not "dog" "cat" or "mouse" the return should be "wild".

Ultimately I would like to only have one column that tests 9 conditions and
has 3 different value outputs, but I think that it is easier for me to build
two columns and split the test.

If anyone has any ideas I would really appreciate it!

 

[Bob Phillips]

=IF(NOT(G2),IF(OR(F2={"cat","dog","mouse"}),"pet","wild"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[SteveG]

What are you testing to return the TRUE or FALSE in G? If you can post
exactly what your tests are and what the results should be, that would
be helpful.

Regards,

Steve

 

[Guest]

There is probably a simpler sollution to this. Im new to Excel. I don't
know what your first Test is susposed to test so in My example I have it
testing if the Column with Data is empty. If it is it does nothing. If it
is false it will begin to see if its a Cat dog or Mouse. Using something
like what I wrote isn't very suitable for expantion though since I beleive
Excel can only have about eleven nested Ifs.

=IF(B11="",,IF(B11="Cat","Pet",IF(B11="Dog","Pet",IF(B11="Mouse","Pet","Wild"))))

 

[Guest]

I have three items that I am testing for. Active, Inactive, and Future data.
Each of those three descriptors have a number of conditions that indicate my
data falls into that category. All of my data has word descriptors but I
thought letters would be easier to follow. My first test separates out
Active data from the Inactive and Future data.
IF(F2="A","Active",IF(F2="B","Active",IF(FE2="C","Active",IF(FE2="D","Active"))))

Then I am trying to build a test to sort through the Inactive and Future
data. The function I was trying to build would take all of the "FALSE"
results from the first test (which is anything that isn't Active) and
separate that out. Something like If G2=FALSE & F2=E or F or G then
Inactive. Also If G2=FALSE & F2=H or I or J then Future.

Does that make more sense? Using housepets to explain what I'm trying to do
is probably more trouble than it's worth.

 

[SteveG]

Mary,

So using your example, IF F2 = A,B,C or D then return Active. IF F2 =
E,F or G then return Inactive. IF F2 = H,I or J then return Future.
Here is kind of a long solution.

=IF(OR(F2="A",F2="B",F2="C",F2="D"),"Active",IF(OR(F2="E",F2="F",F2="G"),"Inactive",IF(OR(F2="H",F2="I",F2="J"),"Future")))

Does that help?

Steve

 

[SteveG]

Another way is you could take make a list of your words in another
column say K2:K11 and their corresponding labels (Active, Inactive or
Future) in L2:L11. In G2,

=IF(ISNA(VLOOKUP(F2,K2:L11,2,FALSE)),"Not
Found",VLOOKUP(F2,K2:L11,2,FALSE))

HTH

Steve

 

[Guest]

Works like a charm. Steve you're amazing!

 

[SteveG]

Glad I could help.

Cheers,
Steve

 

[SteveG]

To shorten it a bit try using part of Bob's post where the values are i
{} so you don't have to type F2= for each one in the OR statement.

=IF(OR(F2={"A","B","C","D"}),"Active",IF(OR(F2={"E","F","G"}),"Inactive",IF(OR(F2={"H","I","J"}),"Future","No
Found")))


Stev