Combining two IF statements into one cell

[jsflbot]

Hello! I'm trying to combine two IF statements but need help on how to do this...

Argument #1: =IF(AND(ISNUMBER(B5),ISBLANK(C5)),"New Fallout","No Change")
Argument #2: =IF(AND(ISBLANK(B5),ISNUMBER(C5)),"New Sale","New Fallout")

I need these two arguments to be combined into one cell, any help is appreciated, thanks!

 

[Joe4]

Welcome to the Board!

The issue, what if neither of the conditions are (i.e. what if BOTH B5 and C5 are numeric, of what if BOTH are blank)?
In those cases, should it return "No change" or "New Fallout"?

 

[jsflbot]

If neither conditions are met, as in, if both cells have blanks or both cells have numbers, then it would say "no change"

 

[Joe4]

Then try this:

=IF(AND(ISNUMBER(B5),ISBLANK(C5)),"New Fallout", IF(AND(ISBLANK(B5),ISNUMBER(C5)),"New Sale","No Change"))

For more on using nested IF statements in Excel, read the following:
http://www.techonthenet.com/excel/formulas/if_nested.php
http://spreadsheets.about.com/od/tipsandfaqs/qt/nested_if.htm
http://office.microsoft.com/en-us/excel-help/if-function-HP010342586.aspx

 

[jsflbot]

awesome! it worked like a charm, thanks Joe4! you're a life saver!

 

[jsflbot]

Joe, how would the formula change if now instead of blanks, I have zero's in place.

I'm running into the problem where if both cells are zero's, it's coming up as NEW FALLOUT, instead of NO CHANGE. This is my current formula:
=IF(AND(ISNUMBER(F11),G11=0),"New Fallout", IF(AND(F11=0,ISNUMBER(G11)),"New Sale","No Change"))

 

[Joe4]

I'm running into the problem where if both cells are zero's, it's coming up as NEW FALLOUT, instead of NO CHANGE. This is my current formula:
=IF(AND(ISNUMBER(F11),G11=0),"New Fallout", IF(AND(F11=0,ISNUMBER(G11)),"New Sale","No Change"))

The problem is if they are both zero, all your conditions are being met. 0 is a number, and 0=0.
So you may want to introduce another clause into your AND statement checking to see if the number is greater than zero, if you do not want it to return "New Fallout".
Note that in AND functions, you can have as many criteria as you want (you are not limited to two).
=AND(condition1, condition2, condition3,...)

 

[jsflbot]

since I'm not very familiar with these types of formulas, where exactly would I put that additional AND clause and in which format?

like this? : =IF(AND(ISNUMBER(F11),G11=0),"New Fallout", IF(AND(F11=0,ISNUMBER(G11)),"New Sale","No Change", IF(AND(F11=0,G11=0),"No Change"))

I entered it and got an error message..

 

[Joe4]

No, you would just add a condition to your current AND, i.e.

[B]=IF(AND(ISNUMBER(F11),G11=0,[COLOR=#ff0000][I]condition3[/I][/COLOR]),"New Fallout", IF(AND(F11=0,ISNUMBER(G11)),"New Sale","No Change"))[/B]

I am leaving it to you to update, as I am unsure which cell you are checking (F11 or G11), as you weren't too specific on that.
So maybe you'll be replacing condition3 with something like F11>0

since I'm not very familiar with these types of formulas, where exactly would I put that additional AND clause and in which format?

You should take the time to try to work through these formulas and really understand what they are doing (otherwise I feel like I am not really helping you learn anything, just spoonfeeding you answers). Excel's built in help files are excellent, as they have detailed descriptions and examples. You can also find many good articles and tutorials on-line, like this one:
Excel AND Function Tutorial

 

[jsflbot]

Hi Joe, I did add the condition to the AND formula, but its not working. I tried F11>0 F11<0 F11=0 and none of them seem to return No Change. Am I doing something incorrectly?