locked
Calculated Field Help RRS feed

  • 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

Answers

  • 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 Prasath C Monday, April 29, 2013 5:18 PM
    • Marked as answer by MikeNJ00 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 Prasath C Monday, April 29, 2013 5:18 PM
    • Marked as answer by MikeNJ00 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