RYG Symbols from 3 Columns

aalang103696

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")))

 

Genevieve P.

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

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").

aalang103696

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!