RYG Symbols from 3 Columns

aalang103696
aalang103696 ✭✭✭
edited 12/09/19 in Formulas and Functions

I am trying to return a RYG symbol based on 3 different columns(Decision) Approved, Needs Additional Information (Denied), or Submitted. 

Based on these 3 columns data, if any of them say "Need Additional Information", I'd like the Symbol (Status) Column to be the Red Symbol, however if all 3 Decision Columns are "Approved" I'd like the symbol to be Green, and if any of the 3 columns show the Decision as "Submitted" I'd like the "Status" Column to show Yellow. 

 

I have the current formula based on just the "Challenger Decision" Column but cannot figure out how to add the AND/If statement to return the resulting symbol. 

 

=IF([Challenger Decision]2 = "Approved", "Green", IF([Challenger Decision]2 = "Need Additional Information", "Red", IF([Challenger Decision]2 = "Submitted", "Yellow", "Gray")))

 

Capture3.PNG

Comments

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 09/23/19

    Your formula looks great! You're already 1/3 of the way there. For what you're looking to do, you would want to try an IF(OR statement for the Yellow and Red status balls, with an IF(AND for the Green one, since you want the criteria to be all of the columns, versus any of them.

    First, sort out your hierarchy of instructions. For example, if one column says "Need Additional Information" but another says "Submitted", what takes precedence? Then, break it out by individual instructions, ordering them by hierarchy:

     

     

    If column 1 OR 2 OR 3 says "Need Additional Information," return a Red ball.

    If column 1 OR 2 OR 3 says "Submitted," return a Yellow ball.

    If column 1 AND 2 AND 3 says "Approved," return Green.

    Otherwise, return a Gray ball.

     

     

    Once you know what you want each instruction to say, you can add them all together in a nested IF statement. I have done an example formula based on your column names that you may want to try if the Red status is the most important:

     

    =IF(OR([Challenger Decision]@row = "Need Additional Information", [Finance Decision]@row = "Need Additional Information", [Procurement Decision]@row = "Need Additional Information"), "Red", IF(OR([Challenger Decision]@row = "Submitted", [Finance Decision]@row = "Submitted", [Procurement Decision]@row = "Submitted"), "Yellow", IF(AND([Challenger Decision]@row = "Approved", [Finance Decision]@row = "Approved", [Procurement Decision]@row = "Approved"), "Green", "Gray")))

     

     

     

    I used @row to reference the row number to increase efficiency. Please also note that this formula would return a Gray status ball if only the first or second columns were "Approved", yet there is nothing in the last column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also use the CONTAINS function in place of the OR functions, and a DISTINCT function in place of the AND.

     

    =IF(CONTAINS("Need Additional", [Challenger Decision]@row:[Procurement Decision]@row), "Red", IF(CONTAINS("Submitted", [Challenger Decision]@row:[Procurement Decision]@row), "Yellow", IF(COUNT(DISTINCT([Challenger Decision]@row:[Procurement Decision]@row)) = 1, "Green")), "Gray")

    .

    Basically the CONTAINS function looks across all three columns in that row for any instance of the specific text. If it finds it, then it returns a "true" value and will display the color specified.

     

    The COUNT(DISTINCT(...........)) combo in place of the AND function basically counts how many values within the specified range (the same three columns in the same row are unique. If all three columns match, the the statement will return a 1.

     

    Since we have already specified for "Red" and "Yellow", the formula will only proceed this far if both of those are false meaning "Need Additional" and "Submitted" are not present (which means they are all three "Approved").

  • That worked perfectly! I was super close just couldnt figure out how to add in the AND/OR statement when I kept rewriting it. 

     

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!