Solved Excel Nested IF/AND Statements

March 2, 2009 at 09:22:56
Specs: Windows XP, 1.0 Gb

I have two IF/AND statements that both work independently of each other but I need to combine them into one statement. I tried several different ways of combining them but keep on getting error messages:

=if(and(g4="hn", g9="er"),"20.33","30.50")

=if(and(g4="cl", g9="er'), "18.13", "27.20")

Thank you.


See More: Excel Nested IF/AND Statements

Report •


#1
March 2, 2009 at 11:44:58

I don't think they can be combined based on the criteria given. Let's break them down in detail...

=if(and(g4="hn", g9="er"),"20.33","30.50")

If the AND conditions are met, display 20.33
If not, display 30.50

=if(and(g4="cl", g9="er'), "18.13", "27.20")

If the AND conditions are met, display 18.13
If not, display 27.20

So if neither of the two sets of AND conditions are met, do we display 30.50 or 27.20? You can't have both.


Report •

#2
March 2, 2009 at 14:55:08

Dear DerbyDad03,

Thank you for the quick response. Perhaps I didn't explain my question correctly. In G4, I can input either "hn" or "cl" and in G9, I can input either "er" or "ep". Therefore, there are four possible combinations: "hn" and "er", "hn and "ep", "cl and er", and "cl" and "ep". "hn" and "er" should produce "20.33" in a given cell; "hn" and "ep" should produce "30.50" in a given cell; "cl" and "er" should produce "18.13" in a given cell and "cl" and "ep" should produce "27.20" in a given cell.

Brian W


Report •

#3
March 2, 2009 at 16:17:40
✔ Best Answer

You need to use what is known as a Nested IF.

The value_if_false for each IF is another IF function.

This should get you started:

=if(and(g4="hn",g9="er"),20.33,(if(and(g4="hn",g9="ep"),30.50,(if(and( etc.


Report •

Related Solutions

#4
March 3, 2009 at 06:16:19

Thanks for your assistance; after I figured out the parentheses, everything worked correctly.


Report •


Ask Question