RYGG nesting functions-UPDATED

jinden@wchealth
jinden@wchealth ✭✭✭
edited 12/09/19 in Formulas and Functions

EDITED TO ADD:

I was able to figure out this morening w/ fresh eyes/brain and tested all scenarios which tested true:

=IF(OR([STATUS]@row="Barrier",AND([Due Date]@row<TODAY(),NOT([STATUS]@row="Completed"))), "Red",IF([STATUS]@row="completed", "Green",IF(AND([Due Date]@row<TODAY(+3),[STATUS]@row="Not Started"),"Yellow", "Gray"))))

 

BUT then i figured out i want task health to be blank if no due date is entered (i.e: no task essentially)

worked out and tested true: =IF([Due Date]@row = "empty", "blank")

Now i cant figure out how to add it into the above nesting...ugh

 

we're having trouble w/ the nesting of our functions

in working w/ my data analyst coworkers we wrote these original 4 functions (each tested properly on its own)

ORIG

  1. =IF(OR(Status@row = "Barrier", AND([Due Date]@row < TODAY(), NOT(Status@row = "Completed"))), "Red")
  2. =IF(AND([Due Date]@row > TODAY(+3), Status@row = "Not Started"), "Yellow")
  3. =IF([Status]@row="completed","Green")
  4. =IF([DueDate]@row=&lt;TODAY(+2), "Grey")

We then combined #2&4 into one and came up w/ the following revision (all tested properly on its own)

REVISION

  1. IF([STATUS]@row="completed"), "Green"
  2. IF(AND([Due Date]@row&lt;TODAY(+3),[STATUS]@row="Not Started"),"Yellow", "Gray")
  3. IF(OR([STATUS]="Barrier",AND([Due Date]<TODAY(),NOT([STATUS]="Completed"))), "Red"

BUT we're going crazy trying to figure out how Smartsheets wants it...anything nesting we do comes back Unparseable or Invalid Data Type  any advice?

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!