Combine 4 Individual =IF(AND Formulas In One Cell

March 27, 2013 at 11:48:12
Specs: Windows XP
I'm working on an Excel sheet and I have four =IF(AND( formulas that work individually. However I want the True or False results posted in the same cell - meaning run all 4 formulas on the same cell and have text populate the cell with the correct statement. Basically, if any of the logic tests are true, I get that answer and that answer only Here are the individual formulas that I would like to apply to cell G38:
=IF(AND(G36=J43,G37=J43),"Possibly","No")
=IF(AND(G36=J43,G37=J44),"No","Possibly")
=IF(AND(G36=J44,G37=J43),"Possibly","CBA")
=IF(AND(G36=J44,G37=J44),"No","No")
Definitions:
G36 Formula: =IF(B6>B3,"Yes","No")
J43 is just text
G37 formula: =IF(B16>B5,"Yes","No")
J44 is just text
Any help regarding this matter will be greatly appreciated.
Thank you in advance for your time and effort, whoever answers.

See More: Combine 4 Individual =IF(AND Formulas In One Cell

Report •


#1
March 27, 2013 at 12:12:00
Well, before we put any work into this, please explain this formula:

=IF(AND(G36=J44,G37=J44),"No","No")

What's the point of an IF formula that will always return No?


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


Report •

#2
March 27, 2013 at 12:19:36
Excellent point! I am covering 2 seperate correct answers and they both happen to be "No. I'm new to this and I was keeping consistent with what I was given to work with. The matrix was Possibly or No; No or Possibly; Possibly or CBA; or No and No. Actually i didn't even think that it didn't make a difference. Good catch.

Report •

#3
March 27, 2013 at 12:54:32
Besides my question about the formula that always returns No, I think I see another problem:

Let's look at 2 cases where the formulas will return "Possibly"

=IF(AND(G36=J43,G37=J43),"Possibly","No")
=IF(AND(G36=J44,G37=J43),"Possibly","CBA")


AND(G36=J43,G37=J43) ---> Possibly
AND(G36=J44,G37=J43) ---> Possibly

Now, since G36 and G37 can only contain Yes or No because of the formulas that are in them, let's put No in J43 and J44, so that both of those IF(ANDs return 'Possibly"

G36 = J43 = No AND G37 = J43 = No ---> Possibly
G36 = J44 = No AND G37 = J43 = No ---> Possibly

OK, so here's the other case where you want the combined formula to return Possibly:

=IF(AND(G36=J43,G37=J44),"No","Possibly")

In this case, the AND portion must be FALSE in order to get Possibly, which means that one of those pairs has to be FALSE. However, since we put No in all of those cells, that formula is going to return No.

G36 = J43 = No AND G37 = J44 = No ---> No

In other words, I don't see how you can combine all of the formulas when the same set of circumstances returns 2 different values when formulas are used separetly.

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


Report •

Related Solutions

#4
March 27, 2013 at 13:36:05
A-ha! Excellent observation. I guess I would have to rewrite the formula(s). Would you be open to taking this off-line? I could email you my abbreviated worksheet and let you know what I would like to do and you can teach me how to do it. I would greatly appreciate it and will "buy you a cold one" (so to speak) if we ever meet. I would very much appreciate it. Thank you!

Report •

#5
March 27, 2013 at 15:09:14
I hate to say no, but I am currently in the middle of 2 other off-line projects in this forum, as well as my full time job, as well as trying to do taxes for myself and 3 kids as well as trying to spend a modicum of time with my wife over this Easter weekend.

Maybe someone else can step up, but I'm too buried right now to take on another project.

Quick hits for formula help is possible, but nothing more than that. Sorry.

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


Report •

#6
March 27, 2013 at 15:22:28
I completely understand and thank you very much for all of your time and effort. Enjoy your holiday weekend! Again, thank you! JohnB

Report •

#7
March 28, 2013 at 07:51:16
OK, how would I write this formula?
I have 4 criteria that would render a Y / N table like this:
YY
YN
NY
NN
=IF(AND(G36=J43,G38=J43),"Possibly","")
=IF(AND(G36=J43,G38=J44),"No","")
=IF(AND(G36=J44,G38=J43),"Possibly",'')
=IF(AND(G36=J44,G38=J44),"No","")
Definitions:
J43=Yes
J44=No
G36= Yes or No
G38= Yes or No
This should work but I don't know how to write the multiple formulas together. They work individually.
Thanks,
JohnB

Report •

#8
March 28, 2013 at 10:46:16
Figured it out! Thanks for the help!

Report •


Ask Question