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!

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mary s" wrote in message
...
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!

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=541644

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"))))

"mary s" wrote:

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!

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" wrote:


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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=541644

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
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=541644

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=541644

Works like a charm. Steve you're amazing!

"SteveG" wrote:


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
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=541644

Glad I could help.

Cheers,
Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=541644

To shorten it a bit try using part of Bob's post where the values are in
{} 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"}),"Fu ture","Not
Found")))


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=541644