|
If Then, not using values, or not counting blanks
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? |
Does this work for you?
=IF(AND(E4="Y",F4="Y"),"P","D") -- Ken Hudson "Need Help 123" wrote: 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? |
Sorry, mis-read part of your question.
=IF(AND(E4="",F4=""),"",IF(AND(E4="Y",F4="Y"),"P", "D")) -- Ken Hudson "Need Help 123" wrote: 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? |
Yes this is possible and the following is one of the possible solutions (on the presumption that when both the columns E and F are blanks then 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 Agarwal -- agarwaldvk ------------------------------------------------------------------------ agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345 View this thread: http://www.excelforum.com/showthread...hreadid=388539 |
G2:
=LOOKUP(E2&F2,{"N","";"NN","";"NY","D";"Y","";"YN" ,"D";"YY","P"}) Need Help 123 wrote: 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. |
Try
=IF(AND(E2="Y",F2="Y"),"P",IF(OR(AND(E2="Y",F2="N" ),AND(E2="N",F2="Y")),"D","")) -- Regards Roger Govier "Need Help 123" <Need Help wrote in message ... 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? |
Thanks! I would have never figured that one out!
"Max" wrote: 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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Need Help 123" <Need Help wrote in message ... 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? |
You're welcome !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Need Help 123" wrote in message ... Thanks! I would have never figured that one out! |
| All times are GMT +1. The time now is 03:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com