combine two if statements excel - based on condition

0
votes

I am trying to find a way to combine these into one formula in excel. I have tried many variations and splitting each into a separate true/false without any luck of consolidating to one answer for B2= "Preferred" vs B2 = "Bond/Note" (Note I am using Excel 2010).

There are two criteria I need to test against if B2 is Preferred or Bond. I do not want to add more columns for each as there is a lengthy VBA code that I do not want to rewrite/break by changing the structure of the sheet.

=IF(AND(B2="Preferred", F2<>"Unrated",F2<>"BIG",F2<>"Data Not Found",OR(H2<31), OR(K2<>"Y")),"Y", "N")

=IF(AND(B2="Bond/Note", F2<>"Unrated",F2<>"BIG",F2<>"Data Not Found", OR(K2<>"Y")),"Y","N")
1
excelif-statementexcel-formula
How are you trying to combine these? What's the ultimate goal? I think you can probably use a VLOOKUP()? - BruceWayne
The ultimate goal is one end column with a Y or N based on the criteria. The criteria for the Preferred needs to include one extra scenario that my Bond doesn't have to have. - megiano3
what are you doing here: OR(H2<31), OR(K2<>"Y")? - Scott Craner
Sometimes it helps to write out what you want to do in total. It also helps to figure out what are the most important decisions. Are you first comparing "Preferred" vs "Bond/Note" or is it more important to first decided that F2<>"Unrated" as an example? Just the process of doing this can sometimes reveal an answer you can use best. - David S

1 Answers

0
votes

Oops, answered in the comment.

I haven't tested this, but it should do. =IF(AND(OR(AND(B2="Preferred", H2<31), B2="Bond/Note"), F2<>"Unrated",F2<>"BIG",F2<>"Data Not Found", K2<>"Y"),"Y", "N")

=IF(AND(B2="Preferred", F2<>"Unrated",F2<>"BIG",F2<>"Data Not Found",OR(H2<31), OR(K2<>"Y")),"Y", "N") combined with =IF(AND(B2="Bond/Note", F2<>"Unrated",F2<>"BIG",F2<>"Data Not Found", OR(K2<>"Y")),"Y","N") is only really one added criteria.

Here I combined them by bringing the difference AND(B2="Preferred", H2<31) to be tied together. So only if bother are true will it proceed with "Preferred".

Then OR(AND(B2="Preferred", H2<31), B2="Bond/Note") gives B2 being either or. This is wrapped into the AND statement that includes the other required criteria and you have a total statement that does both IF statements in one.