# Calculated Field Help

• ### Question

• Hi,

I'm having problem coming up with formula for a calculated field.

I have 4 columns I want to base this field on.  The options for these 4 columns are a dropdown with these as options

Approved (non contingent on actions)
Approved with Actions (contingent on completing actions)
Deferred/Request to come back
Non-Compliant ( Not Following Portfolio Governance Process)

I want the calculated field to say "Yes"  if all four columns contain the word "Approved" in them and "No"  If the four columns don't contain the word "Approved"  Can anyone help?

Thanks

Monday, April 29, 2013 2:07 PM

• use a combination of the formula IF and Search

Search gives the position where the value starts when there is no match it give #value!

there fore check with Isnumber if it gives a number back

`=IF(ISNUMBER(SEARCH("Approved",[field01],1)),"Yes","No")`

for check the four columns use the function AND

`=IF(AND(ISNUMBER(SEARCH("Approved",[field01],1)),ISNUMBER(SEARCH("Approved",[field02],1)),ISNUMBER(SEARCH("Approved",[field03],1)),ISNUMBER(SEARCH("Approved",[field04],1))),"Yes","No")`

hope this help

/blog twttr @esjord

• Proposed as answer by Monday, April 29, 2013 5:18 PM
• Marked as answer by Monday, April 29, 2013 6:09 PM
Monday, April 29, 2013 5:09 PM

### All replies

• use a combination of the formula IF and Search

Search gives the position where the value starts when there is no match it give #value!

there fore check with Isnumber if it gives a number back

`=IF(ISNUMBER(SEARCH("Approved",[field01],1)),"Yes","No")`

for check the four columns use the function AND

`=IF(AND(ISNUMBER(SEARCH("Approved",[field01],1)),ISNUMBER(SEARCH("Approved",[field02],1)),ISNUMBER(SEARCH("Approved",[field03],1)),ISNUMBER(SEARCH("Approved",[field04],1))),"Yes","No")`

hope this help

/blog twttr @esjord

• Proposed as answer by Monday, April 29, 2013 5:18 PM
• Marked as answer by Monday, April 29, 2013 6:09 PM
Monday, April 29, 2013 5:09 PM
• Awesome!  Yes that works, thank you!
Monday, April 29, 2013 5:13 PM
• you're welcome :) glad I could help

/blog twttr @esjord

Monday, April 29, 2013 6:14 PM