RYGB Formula

Peggy Parchert

I'm trying to get a RYGB formula to work. I'm thinking my order is wrong but I'm not sure. I had it working for RYGG but was asked to show Completed Tasks in a different color. I would like to change this so that Blue = Complete and N/A and Not Started are blank.

Formula that is currently working for RYGG:

=IF(Status@row = "On Hold", "Yellow", IF(OR(AND(TODAY() >= [End Date]@row, Status@row <> "Complete"), Status@row = "At Risk"), "Red", IF(OR(Status@row = "Complete", AND(Status@row = "On Track/In Progress", [End Date]@row >= TODAY())), "Green", IF(OR(Status@row = "N/A", AND(OR(ISBLANK(Status@row), Status@row = "Not Started"), [End Date]@row > TODAY())), "Gray"))))

Criteria:

RYGG = Red

1) If End Date is in the past and Status is not “Complete” or 2) if Status is “At Risk” or 3) if End Date is in the past and Status is blank

RYGG = Yellow

If Status is “On Hold”

RYGG = Green

1) If Status is “Complete” or 2) if End Date is in the future and Status is “In Progress”

RYGG = Gray

1) If End Date is in the future and Status is “Not Started” or 2) if Status is “N/A” or 3) if End Date is in the future and Status is blank

Any assistance would be greatly appreciated.

Thanks! 

L_123

=IF(Status@row = "Complete", "Green", IF(Status@row = "At Risk", "Red", IF(Status@row = "On Hold", "Yellow", IF(Status@row = "N/A", "Grey", IF([End Date]@row < TODAY(), "Red", IF(Status@row = "In Progress", "Green", "Grey"))))))

L_123

I recommend making color pallet like I did above. It makes problems like this much more intuitive to solve. I took the liberty of saying today is in the future as that wasn't really acknowledged in your question. I hope this works for you, it cuts off a lot of the complexity of using all those AND and OR statements.

Peggy Parchert

Thank you. Appreciate the input. I had used the AND and OR statements so that if a Task Status was "In Progress" but the End Date for that Task was in the past (which would mean that the RYGB ball should be Red, not Green). So I would need to put that back in, correct?

Actually I just realized I didn't give you the correct criteria above.

Criteria:

RYGB = Red

1) If End Date is in the past and Status is not “Complete” or 2) if Status is “At Risk” or 3) if End Date is in the past and Status is blank

RYGB = Yellow

If Status is “On Hold”               

RYGB = Green

1) If Status is “Complete” or 2) if End Date is in the future and Status is “In Progress”               

RYGB = Complete

If Status is “Complete” - End Date could be in past or in the future 

RYGB = blank

1) If End Date is in the future and Status is “Not Started” or 2) if Status is “N/A” or 3) if End Date is in the future and Status is blank

L_123

The And statement is implied through the flow of the if statement. Example:

 

=If(A1 = true, if(B1 = True,0,1),if(B1 = false,2,3))

In this statement, there are 2 if statements down the flow from the first one. That means criteria has to be met before the if statement is reached. So for the second if statement (B1 = True), you can infer the and statement. if(and(A1 = true,B1 = true).Due to this property you can simplify your if statements by stacking your if statements in the most efficient manner. The most logical way to do this is to map out the potential responses, which I have done below

This is a simplified version of the answer I gave you. In this there are 2 variables (A1 and B1) that can each have 2 values (true, false). So I need to have 2 * 2 = 4 potential solutions to account for every possible return.

--------------A1True----A1False

B1True          0              2

B1False        1              3

I'll take a look at the new criteria and post a response in a minute

L_123

Alright i'm confused about your criteria. Are we not doing RGBG anymore? Now you have 2 new responses, "Complete" and "Blank", but complete shares the criteria with green. If you could fill out the color pallet and attach it I would appreciate it. You can use excel or google sheets or even smartsheet and just format it just like the first one I posted so our criteria is clear.

Peggy Parchert

Thank you for your patience. I apologize for any confusion. Below is the correct criteria:

 

RYGB column = Red

1) If End Date is in the past and Status is not “Complete” (Status is: In Progress, Not Started or Blank) or 2) if Status is “At Risk”

RYGB column = Blue

If Status is “Complete” - End Date could be in past or in the future

RYGB column = Yellow

If Status is “On Hold”         

RYGB column = Green

1) if End Date is in the future and Status is “In Progress”              

RYGB  column will be blank:

1) If End Date is in the future and Status is “Not Started” or 2) if Status is “N/A” or 3) if End Date is in the future and Status is blank

 

I've attempted to create the visual as you suggested. I've attached this. 

L_123

alright, I would tackle the ones that post no matter the time first.

=if(status1 = "complete","Blue",if(status1 = "At Risk", "Red",if(status1 = "On Hold","Yellow",

Once you are at this point, the next criteria will have the condition that Status is not complete, at risk, or on hold. So we can count those out of our next calculations. The rest of the criteria is different between future and past, so we need to split it with the next if statement

=if([End Date]1 < today(), all returns for past, all returns for future)

All returns for Past

the only criteria that is not red is "N/A". so lets sort out the "N/A" from the others, then post that "N/A" is blank, and everything else is red

=if(Status1 = "N/A","","Red")

That completes everything in the past. So now for the future.

All returns for Future

The only item that has a color is "in progress", everything else that we haven't accounted for is blank. So lets parse out "in progress" and say everything else is blank

=if(Status1 = "In Progress", "Green","")

 

Now we need to put everything together.

=if(status1 = "complete","Blue",if(status1 = "At Risk", "Red",if(status1 = "On Hold","Yellow",if([End Date]1 < today(), if(Status1 = "N/A","","Red"), if(Status1 = "In Progress", "Green",""))

 

Potentially a typo in there, didn't put it in smartsheet to double check.

Peggy Parchert

Thank you. Again apologize for the confusion.