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.