if, or, and-formula issue

[Swifty87]

Hey All,

I am scratching my head trying to figure out where i am going wrong, looking for a cell to return '1' or '0' based if certain combinations of Yes/No and Blank cells are selected for the logic.

Formula i have now is below, but this issue seems to be that it is not working for the if cell is blank '="<>"&"*"'

=IF(OR(AND(C4="No",C5="yes"),AND(C4="No",C5="No"),AND(Steps!$C$4="<>"&"*",C5="No"),AND(Steps!$C$4="No",C5="<>"&"*"),AND(Steps!$C$4="<>"&"*",C5="<>"&"*")),"1","0")

combinations for returning '1' are:
C4="No"
C5="No"
or
C4="No"
C5="Yes"
or
C4= Empty Cell
C5= Empty Cell
or
C4= Empty Cell
C5= "Yes"

combinations for returning '0' are:
C4="Yes"
C5="No"
or
C4= "Yes"
C5= Empty Cell

Any help would be greatly appreciated.

Thanks

 

[mikerickson]

There are two situations that haven't been defined. What result do you want if
c4="No" C5 is empty
c4 is empty, C5 = "No"


So far, with the conditions listed in post 1

=1-(C4="Yes")

would do what you want.

 

[Swifty87]

There are two situations that haven't been defined. What result do you want if
c4="No" C5 is empty
c4 is empty, C5 = "No"


So far, with the conditions listed in post 1

=1-(C4="Yes")

would do what you want.

Thanks for your reply Mike,

My god i have been way over complicating things!! What you have given me works perfect.

if c4="No" C5 is empty, and c4 is empty, C5 = "No" I would want it to return "1", this does this.

Thank you Mike, if you don't mind for my benefit any chance you can quickly run through how this works for my understanding?

Once again thanks for the help

 

[mikerickson]

Excel worksheet formulas convert TRUE and FALSE to 1 and 0.