Formula to Produce Symbol based on other Symbols

Taylar LaBonte

I have a very complex formula I need help with... using dates and symbols in other columns to dictate the symbol produced.

The 'LIVE' column below will populate with green, yellow, red, or grey based on a combination of the following specifications:

1) If Target Go-Live date is passed AND Kick-Off is green, AND MSA is green, AND Users is green, AND Go-Live Training is green, THEN Live = green

2) If Target Go-Live date is not passed AND any/all of Kick-Off, MSA, Users are green/yellow/red, THEN Live = yellow

3) If Target Go-Live date is passed AND Kick-Off is yellow, AND MSA is green, AND Users is green, AND Go-Live Training is green, THEN Live = red

3a) If Target Go-Live date is passed AND any of Kick-Off, MSA, Users are yellow/red, THEN Live = red

4) If Target Go-Live date is NA AND Kick-Off is grey, AND MSA is grey, AND Users is grey AND Go-Live Training is grey, THEN Live = grey

Example of view below, note that each column can be green/yellow/red/grey.

Essentially,

1) If any of the tasks (Kick-Off, MSA, Users, Go-Live training) are NOT Green, and the target date is passed, LIVE is RED.

2) If any of the tasks (Kick-Off, MSA, Users, Go-Live training) are NOT Green, and the target date is NOT passed, LIVE is YELLOW.

3) If all of the tasks (Kick-Off, MSA, Users, Go-Live training) ARE Green, and the target date IS passed, LIVE is GREEN.

If someone can help... I will bake you cookies!!

James Keuning

for number three, one column, it's a symbol:

=IF(AND([2 test]@row = true, [2.a test]@row = false), "Red")

Your 3a example includes your 3 example in the results

If you have gotten this far and things are working, you can now go in and replace the references to the helper columns with the values of the helper columns.

In other words, that formula up there would become:

=IF(AND((3 > COUNTIFS([Kick-off]@row:[Go-Live Training]@row, "Green")), ([Target Go-Live]@row > TODAY()) = false), "Red")

James Keuning

Major edit: I think to test the first condition, you just test to see if everything is green. If everything is green, Live is green. We do not need to check the date*, so:

=IF(3 < COUNTIFS([Kick-off]@row:[Go-Live Training]@row, "green"), "Green")

That test the first condition. If you add criteria, you will need to edit this. This formula looks to see if there are less than 3 greens, if not, then Live is green.

*if you do want to check the date, because you want to do that gray stuff, then you can't use this alternative.

~~~~~~~~~~~~~~~~~~~pre-major edit~~~~~~~~~~~~~~~~

Here is how I would test for number one. Build a helper column for each test. Then a helper column to evaluate that all of the test answers combine to provide the color for the Live field. The Live field will be an OR statement that looks at all of the test results for your 1-4.

So, for number one:

Create 3 columns, all column-type checkbox:

1 test
1.b test
1.c test

1 test - checks to see if Target Go-Live date is passed

=[Target Go-Live]@row < TODAY()

1.b test - checks to see if all of the columns are green

=(AND([Kick-off]@row = "green", MSA@row = "green", Users@row = "green", [Go-Live Training]@row = "green"))

OR (if you use this alternative, and you add criteria, you will need to edit this. This formula looks to see if there are less than 3 greens.)

=3 < COUNTIFS([Kick-off]@row:[Go-Live Training]@row, "green")

1.c test - checks to see if both 1 test and 1.b test are both TRUE

=AND([1 test]@row = true, [1.b test]@row = true)

or better yet, change 1.c column type to Symbol, and use:

=IF(AND([1 test]@row = true, [1.b test]@row = true), "Green")

Again, don't use this result for the Live yet.

James Keuning

For your second test, create three columns:

2 test
2.a test
2.b test

2 test (checkbox): test to see if date is passed

=[Target Go-Live]@row > TODAY()

2.a test (checkbox): check to see if there is any non-green

=3 >= COUNTIFS([Kick-off]@row:[Go-Live Training]@row, "Green")

2.b test (Symbol): test to see if those first two things are true, if so, Yellow.

=IF(AND([2 test]@row = true, [2.a test]@row = true) = true, "Yellow")

So now you have your second test

James Keuning

for number three, one column, it's a symbol:

=IF(AND([2 test]@row = true, [2.a test]@row = false), "Red")

Your 3a example includes your 3 example in the results

If you have gotten this far and things are working, you can now go in and replace the references to the helper columns with the values of the helper columns.

In other words, that formula up there would become:

=IF(AND((3 > COUNTIFS([Kick-off]@row:[Go-Live Training]@row, "Green")), ([Target Go-Live]@row > TODAY()) = false), "Red")

Taylar LaBonte

@James Keuning You are my hero!! It works!!

Thank you so much for taking the time to walk through all of that. Definitely a lot of manual formula creation... but massively pays off in the end!

Thank you so much- I really appreciate it!