If Then, not using values, or not counting blanks

[Guest]

In column E I will be inputting a Y for 'Yes' and an N for 'No'
In column F the same
In column G I would like to have a formula that would do the following:

If there is a Y in E2 and a Y in F2, then insert a P in G2...
If there is a Y in E2 and an N in F2, then insert a D in G2...
If there is an N in E2 and a Y in F2, then insert a D in G2

My problem right now is I am using the formula: IF E2=F2 is True input P in
cell G2, if False input D in cell G2.... but since I am not using values when
E2 and F2 are blank it puts a P in G2 and I want G2 to stay blank if I did
not put anything in E2 or F2... Is this possible?

 

[Guest]

Does this work for you?

=IF(AND(E4="Y",F4="Y"),"P","D")

 

[Max]

One way ..

Put in G2:

=IF(OR(E2="",F2=""),"",IF(COUNTIF(E2:F2,"Y")=2,"P",IF(OR(AND(E2="Y",F2="N"),
AND(E2="N",F2="Y")),"D","")))

Copy G2 down

The first part: =IF(OR(E2="",F2=""),"", ...
is one way to set it up check that
both E2 & F2 are filled before evaluating any further

 

[Guest]

Sorry, mis-read part of your question.

=IF(AND(E4="",F4=""),"",IF(AND(E4="Y",F4="Y"),"P","D"))

 

[agarwaldvk]

Yes this is possible and the following is one of the possible solution
(on the presumption that when both the columns E and F are blanks the
column G would remain blank) :-

Enter this formula in G2

=IF(AND(NOT(ISBLANK(E2)),NOT(ISBLANK(F2))), IF(E2 = F2, "P", "D"), "")


Best regards


Deepak Agarwa

 

[Aladin Akyurek]

G2:

=LOOKUP(E2&F2,{"N","";"NN","";"NY","D";"Y","";"YN","D";"YY","P"})

In column E I will be inputting a Y for 'Yes' and an N for 'No'
In column F the same
In column G I would like to have a formula that would do the following:

If there is a Y in E2 and a Y in F2, then insert a P in G2...
If there is a Y in E2 and an N in F2, then insert a D in G2...
If there is an N in E2 and a Y in F2, then insert a D in G2

My problem right now is I am using the formula: IF E2=F2 is True input P in
cell G2, if False input D in cell G2.... but since I am not using values when
E2 and F2 are blank it puts a P in G2 and I want G2 to stay blank if I did
not put anything in E2 or F2... Is this possible?

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Roger Govier]

Try
=IF(AND(E2="Y",F2="Y"),"P",IF(OR(AND(E2="Y",F2="N"),AND(E2="N",F2="Y")),"D",""))

 

[Guest]

Thanks! I would have never figured that one out!

One way ..

Put in G2:

=IF(OR(E2="",F2=""),"",IF(COUNTIF(E2:F2,"Y")=2,"P",IF(OR(AND(E2="Y",F2="N"),
AND(E2="N",F2="Y")),"D","")))

Copy G2 down

The first part: =IF(OR(E2="",F2=""),"", ...
is one way to set it up check that
both E2 & F2 are filled before evaluating any further

 

[Max]

You're welcome !