RYGB Formula

Peggy Parchert
Peggy Parchert ✭✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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! 

Tags:
· Share on FacebookShare on Twitter

Comments

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

    RYG.JPG

    · Share on FacebookShare on Twitter
  • L_123
    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.

    · Share on FacebookShare on Twitter
  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    edited 03/26/19

    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

    · Share on FacebookShare on Twitter
  • L_123
    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

    · Share on FacebookShare on Twitter
  • L_123
    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.

    · Share on FacebookShare on Twitter
  • Peggy Parchert
    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. 

    Screen Shot 2019-03-27 at 4.08.49 PM (2).png

    · Share on FacebookShare on Twitter
  • L_123
    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.

    · Share on FacebookShare on Twitter
  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    Thank you. Again apologize for the confusion.

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video