if statement

[Portuga]

Hi,

I have the following (partial list) on column A

A1: Count(R14_completeness_BAL_AMT)
A2: Count(R500_consistency_BAL_LIMIT_AMOUNT)
A3: Count(R501_conformity_LIMIT_AMT)
A4: Count(R127_integrity_AVAIL_BAL_AMT_BAL_AMT)
A5: Count(R538_accuracy_srce_sys)
etc


I would like to have the following "if" statement in B1, with the
following 5 conditions:

if A1 contains "Completeness" then "inc";
if A1 contains "Consistency" then "inc";
if A1 contains "Conformity" then "non";
if A1 contains "Integrity" then "bad";
if A1 contains "Accuracy" then "ina";

Thanks!

 

[wjohnson]

Try the following:
I used an "IF/OR" because you have 2 conditions which can equal "inc"
The last item "CHECK" can be anything you want, this will "flag" you if
your cell contains anything other than the 5 conditions.
=IF(OR(A1="Completeness",A1="Consistency"),"Inc",IF(A1="Integrity","bad",IF(A1="Accuracy","ina",IF(A1="Conformity","non","Check"))))

 

[Tom Ogilvy]

Are these literal strings in A1:A5 or part of formulas.

If literal strings, then look at the Search function in Excel VBA help.

But if you have more than 5 strings you are looking for, it might be better
to create a contingency table and use a lookup formula.

However, since it isn't clear what you actually have, it would be better if
your clarify.