been having trouble with this formula.

=IF(AND([Competency Class 1]="Fail","Pending Completion")), (AND([Competency Class 2]="Fail","pending Completion"))

I am looking to have the formula look at three columns and return "Pending Completion" if column has Fail, Hold and if pass on all mark "Completed".

New contributor
Don is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Would it be accurate to restate the requirement as "return 'Pass' when all three columns equal 'Completed'; otherwise, return 'Fail'"? Also, you only have two columns in your posted formula, is it safe to assume the third one is named Competency Class 3? – willman Feb 13 at 0:47

Try this:
=IF(AND([Competency Class 1]="Completed",[Competency Class 2]="Completed",[Competency Class 3]="Completed"), "Pass", "Fail")

Note that IF takes exactly three parameters, the first being the statement to evaluate, the second being what to return when that statement is true and the third being what to return when that statement is false. Although any one of those parameters could be another nested function/formula.

The AND function, on the other hand, can take numerous parameters, and will only return true when every parameter individually evaluates to true.

Not the answer you're looking for? Browse other questions tagged or ask your own question.