Solved Nested IF statement with words not values

July 31, 2012 at 21:00:31
Specs: Windows 7
Hi,

I am having some problems with the best way to make my problem work. I think I will need a nested if statement but, nothing I have been able to find online has helped make it work.


IF (G5="NO") & (G6= "YES") THEN = "0"
IF (G5="YES") & (G6= "NO") THEN = H14
IF (G5="YES") & (G6= "YES") THEN = "INVALID"

I would appreciate some help on a solution in Excel 2010.Thanks!


See More: Nested IF statement with words not values

Report •

✔ Best Answer
August 1, 2012 at 11:45:10
Both of these should. Obviously the first one is shorter, but I included the second one just to show you how the AND function can be used within an IF function when you need to check 2 conditions.

You should not simply use the fomulas as written, you should study them and understand why they work. Following the value_if_true and value_if_false paths for each IF function should help you understand the logic.

Feel free to come back if you can't figure out what's going on.

=IF(G5="YES",IF(G6="YES","INVALID",H14),IF(G6="YES",0,""))

=IF(AND(G5="YES",G6="YES"),"INVALID",IF(AND(G5="NO",G6="NO"),"",IF(G5="YES",H14,0)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 1, 2012 at 00:30:36
This should get you what you asked for:

=IF(G5="YES",IF(G6="NO",H14,0),"INVALID")

However, you didn't specify what you want if G5 or G6 contain something other than YES or NO.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 1, 2012 at 08:15:34
the only options they will have is the YES or NO. However, I did forget about if they are both NO.

IF (G5="NO") & (G6= "YES") THEN = "0"
IF (G5="YES") & (G6= "NO") THEN = H14
IF (G5="YES") & (G6= "YES") THEN = "INVALID"
IF (G5="NO") & (G6="NO") THEN = BLANK

Is that possible to add that one. I would have never come up with that without your help! Thank you so much!


Report •

#3
August 1, 2012 at 08:28:51
In both of your posts you put quotes around the 0 -- "0"

That syntax will return the Text equivalent of 0, not the number 0.

Which is it that you want?

The reason I ask is this:

If you want it to display the number 0, then you have to have the option to display 0 values on or the cell will be blank. Since "Blank" is now what you what displayed based on your latest condition, I want to make sure that you get what you want.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 1, 2012 at 08:39:07
It would be the value 0.

Report •

#5
August 1, 2012 at 11:45:10
✔ Best Answer
Both of these should. Obviously the first one is shorter, but I included the second one just to show you how the AND function can be used within an IF function when you need to check 2 conditions.

You should not simply use the fomulas as written, you should study them and understand why they work. Following the value_if_true and value_if_false paths for each IF function should help you understand the logic.

Feel free to come back if you can't figure out what's going on.

=IF(G5="YES",IF(G6="YES","INVALID",H14),IF(G6="YES",0,""))

=IF(AND(G5="YES",G6="YES"),"INVALID",IF(AND(G5="NO",G6="NO"),"",IF(G5="YES",H14,0)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 1, 2012 at 12:02:17
WORKS AWESOME!!! Thank you so much! You are a blessing!

Report •

Ask Question