software

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
quot;TRUEquot; or quot;FALSEquot;.
Col H: Test Two--I want to build a conditional formula to test that if the
test in Col. G results in quot;FALSEquot; then it references the data in Col. F and
for all items quot;dogquot; quot;catquot; quot;mousequot; in the same row(from Col. F) then it
returns with quot;petquot;. If the data in Col. G is quot;FALSEquot; but the data in the
same row in Col. F is not quot;dogquot; quot;catquot; or quot;mousequot; the return should be quot;wildquot;.

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={quot;catquot;,quot;dogquot;,quot;mousequot;}),quot;petquot;, quot;wildquot;))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;mary squot; gt; wrote in message
...
gt; I'm trying to create a formula to test multiple situations. I have a mock
up
gt; of my spreadsheet below:
gt;
gt; Col F: data i'm testing
gt; Col G: Test One--I have a nested IF formula here that turns out either a
gt; quot;TRUEquot; or quot;FALSEquot;.
gt; Col H: Test Two--I want to build a conditional formula to test that if the
gt; test in Col. G results in quot;FALSEquot; then it references the data in Col. F
and
gt; for all items quot;dogquot; quot;catquot; quot;mousequot; in the same row(from Col. F) then it
gt; returns with quot;petquot;. If the data in Col. G is quot;FALSEquot; but the data in the
gt; same row in Col. F is not quot;dogquot; quot;catquot; or quot;mousequot; the return should be
quot;wildquot;.
gt;
gt; Ultimately I would like to only have one column that tests 9 conditions
and
gt; has 3 different value outputs, but I think that it is easier for me to
build
gt; two columns and split the test.
gt;
gt; If anyone has any ideas I would really appreciate it!
gt;
gt;
gt;

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: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=541644There 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=quot;quot;,,IF(B11=quot;Catquot;,quot;Petquot;,IF(B11=quot;Dogquot;,quot;Petquot;, IF(B11=quot;Mousequot;,quot;Petquot;,quot;Wildquot;))))

quot;mary squot; wrote:

gt; I'm trying to create a formula to test multiple situations. I have a mock up
gt; of my spreadsheet below:
gt;
gt; Col F: data i'm testing
gt; Col G: Test One--I have a nested IF formula here that turns out either a
gt; quot;TRUEquot; or quot;FALSEquot;.
gt; Col H: Test Two--I want to build a conditional formula to test that if the
gt; test in Col. G results in quot;FALSEquot; then it references the data in Col. F and
gt; for all items quot;dogquot; quot;catquot; quot;mousequot; in the same row(from Col. F) then it
gt; returns with quot;petquot;. If the data in Col. G is quot;FALSEquot; but the data in the
gt; same row in Col. F is not quot;dogquot; quot;catquot; or quot;mousequot; the return should be quot;wildquot;.
gt;
gt; Ultimately I would like to only have one column that tests 9 conditions and
gt; has 3 different value outputs, but I think that it is easier for me to build
gt; two columns and split the test.
gt;
gt; If anyone has any ideas I would really appreciate it!
gt;
gt;
gt;

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=quot;Aquot;,quot;Activequot;,IF(F2=quot;Bquot;,quot;Activequot;,IF(FE2=quot;Cquot;,quot; Activequot;,IF(FE2=quot;Dquot;,quot;Activequot;))))

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 quot;FALSEquot;
results from the first test (which is anything that isn't Active) and
separate that out. Something like If G2=FALSE amp; F2=E or F or G then
Inactive. Also If G2=FALSE amp; 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.

quot;SteveGquot; wrote:

gt;
gt; What are you testing to return the TRUE or FALSE in G? If you can post
gt; exactly what your tests are and what the results should be, that would
gt; be helpful.
gt;
gt; Regards,
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=541644
gt;
gt;


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=quot;Aquot;,F2=quot;Bquot;,F2=quot;Cquot;,F2=quot;Dquot;),quot;Activequot;,IF(OR (F2=quot;Equot;,F2=quot;Fquot;,F2=quot;Gquot;),quot;Inactivequot;,IF(OR(F2=quot;Hquot;,F2= quot;Iquot;,F2=quot;Jquot;),quot;Futurequot;)))

Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: 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)),quot;Not
Foundquot;,VLOOKUP(F2,K2:L11,2,FALSE))

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=541644Works like a charm. Steve you're amazing!

quot;SteveGquot; wrote:

gt;
gt; Mary,
gt;
gt; So using your example, IF F2 = A,B,C or D then return Active. IF F2 =
gt; E,F or G then return Inactive. IF F2 = H,I or J then return Future.
gt; Here is kind of a long solution.
gt;
gt; =IF(OR(F2=quot;Aquot;,F2=quot;Bquot;,F2=quot;Cquot;,F2=quot;Dquot;),quot;Activequot;,IF(OR (F2=quot;Equot;,F2=quot;Fquot;,F2=quot;Gquot;),quot;Inactivequot;,IF(OR(F2=quot;Hquot;,F2= quot;Iquot;,F2=quot;Jquot;),quot;Futurequot;)))
gt;
gt; Does that help?
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=541644
gt;
gt;


Glad I could help.

Cheers,
Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: 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={quot;Aquot;,quot;Bquot;,quot;Cquot;,quot;Dquot;}),quot;Activequot;,IF(OR(F2={quot;E quot;,quot;Fquot;,quot;Gquot;}),quot;Inactivequot;,IF(OR(F2={quot;Hquot;,quot;Iquot;,quot;Jquot;}),quot;Fu turequot;,quot;Not
Foundquot;)))Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=541644