IF or AND help

**ms_excelhelp** · 07-17-2017, 10:36 AM

Hello,

I am not familiar with these functions and I am having trouble trying to get the results I want.

Basically, I have 2 columns that will either say Pass or Fail.
1) If the columns both say Pass, I want a third column to auto populate to $100.
2) If the columns both say Fail, I want a fourth column to auto populate with $100.
3) If they say Pass and Fail, I want a fifth column to auto populate with the words "TBD".

How do I go about doing this? Any help would be much appreciated. Thank you!

**AliGW** · 07-17-2017, 10:39 AM

Let's say your lookup columns are A and B. This in C1:

=IF(AND(A1="Pass",B1="Pass"),100,"")

and this in D1:

=IF(AND(A1="Fail",B1="Fail"),100,"")

and this in E1:

=IF(A1<>B1,"TBD","")

Your columns C and D will be formatted as currency.

**oeldere** · 07-17-2017, 10:43 AM

c1 =if(And($A1="Pass",$B1="Pass"),100,"")

can you make the formula in d1 and e1 by yourself?

**ms_excelhelp** · 07-17-2017, 11:20 AM

Thank you SO much for your fast reply. I spent so long trying to figure this out and now it makes so much sense!

I know that the formulas for #1 and #2 are the exact same thing, however, for whatever reason, I have no results when I try #2. I've tried deleting columns and creating new ones, but it comes out as blank. It actually quickly shows as "$0", then disappears. Do you know why this could be? Is there some sort of formatting I need to check for?

#1 and #3 work perfectly.

**AliGW** · 07-17-2017, 11:38 AM

It sounds like you have the column or w.orkbook formatted to hide zero values - could that be it? If you can't find the issue, attach the workbook here for someone to take a look for you.