Help to Simplify Logic
I'm creating a smartsheet with several columns and created a few formulas that I believe could be simplified to achieve the same result, but I can't seem to think of an easier way.
Can you please try to help me out?
HEALTH COLUMN
=IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN())), "Yellow", IF(CONTAINS("Gray", DESCENDANTS()), "Gray", IF(CONTAINS("Green", DESCENDANTS()), "Green", IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF([Cancelled?]@row = 1, "", IF(Index@row = 0, "Red", IF(Index@row = 1, "Yellow", IF(Index@row = 2, "Green", IF(Index@row = 3, "Gray", ""))))))))))
HEALTH (INDEX COLUMN)
0 = RED
1 = YELLOW
2 = GREEN
3 = GRAY
4 = NOT STARTED = BLANK
COMPLETE FORMULA:
=IF(AND(Start@row = "", [Estimated End Date]@row = "", [% Complete]@row <= 0), 4, IF(AND(Start@row > TODAY(), [% Complete]@row <= 0), 4, IF([% Complete]@row = 1, 2, IF(AND([% Complete]@row > 0.85, [Estimated End Date]@row >= TODAY()), 3, IF(AND(Start@row < TODAY(), [Estimated End Date]@row >= TODAY(+3), [% Complete]@row > 0), 3, IF(AND([Estimated End Date]@row > TODAY(+3), [% Complete]@row > 0, [% Complete]@row < 1), 3, IF(AND(Start@row > TODAY(), [% Complete]@row > 0, [% Complete]@row < 1), 3, IF(AND([% Complete]@row > 0, Start@row = ""), 3, IF(AND([% Complete]@row > 0, ISDATE(Start@row)), 3, IF(AND([Estimated End Date]@row < TODAY(), [% Complete]@row < 1), 0, IF(AND(Start@row < TODAY(), [% Complete]@row <= 0), 1, IF(AND([Estimated End Date]@row <= TODAY(+3), [% Complete]@row <= 0.85), 1))))))))))))
DAYS UNTIL EST. END DATE
=IFERROR(IF([% Complete]@row = 1, "", NETWORKDAY(TODAY(), [Estimated End Date]@row)), "")
STATUS COLUMN
NOT STARTED = 4
IN PROGRESS = 1 or 3
COMPLETED = 2
CANCELLED = BOX TICKED
=IF([% Complete]@row = 1, "Completed", IF([Cancelled?]@row = 1, "Cancelled", IF(AND(Start@row = "", [Estimated End Date]@row = "", [% Complete]@row = ""), "Not Started", IF(AND(ISDATE(Start@row), [Estimated End Date]@row >= TODAY(), [% Complete]@row <= 0), "Not Started", IF(AND(ISDATE(Start@row), [Estimated End Date]@row = "", [% Complete]@row <= 0), "Not Started", IF(AND([% Complete]@row > 0, [Estimated End Date]@row >= TODAY()), "In Progress", IF(AND([% Complete]@row > 0, [Estimated End Date]@row = ""), "In Progress", IF([Estimated End Date]@row < TODAY(), "Late"))))))))
Thanks a lot!!!
Answers
-
HEALTH COLUMN
=IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN()), Index@row = 1), "Yellow", IF(OR(CONTAINS("Gray", DESCENDANTS()), Index@row = 3), "Gray", IF(OR(CONTAINS("Green", DESCENDANTS()), Index@row = 2), "Green", IF(OR(CONTAINS("Red", CHILDREN()), Index@row = 0), "Red", IF([Cancelled?]@row = 1, "", "")))))
I will get back to you on the others.
-
HEALTH (INDEX COLUMN)
=IF(OR(AND(Start@row = "", [Estimated End Date]@row = "", [% Complete]@row <= 0), AND(Start@row > TODAY(), [% Complete]@row <= 0)), 4, IF([% Complete]@row = 1, 2, IF(OR(AND([% Complete]@row > 0.85, [Estimated End Date]@row >= TODAY()), AND([% Complete]@row > 0, OR(AND(Start@row < TODAY(), [Estimated End Date]@row >= TODAY(+3)), Start@row > TODAY(), Start@row = "", ISDATE(Start@row))), 3, IF(OR(AND(Start@row < TODAY(), [% Complete]@row <= 0), AND([Estimated End Date]@row <= TODAY(+3), [% Complete]@row <= 0.85)), 1))))
-
STATUS COLUMN
=IF([% Complete]@row = 1, "Completed", IF([Cancelled?]@row = 1, "Cancelled", IF(OR(AND(Start@row = "", [Estimated End Date]@row = "", [% Complete]@row = ""), AND(ISDATE(Start@row), [Estimated End Date]@row >= TODAY(), [% Complete]@row <= 0), AND(ISDATE(Start@row), [Estimated End Date]@row = "", [% Complete]@row <= 0)), "Not Started", IF(OR(AND([% Complete]@row > 0, [Estimated End Date]@row >= TODAY()), AND([% Complete]@row > 0, [Estimated End Date]@row = "")), "In Progress", IF([Estimated End Date]@row < TODAY(), "Late")))))
-
I have provided variations on three of your formulas. If you are able to explain in more detail how each of the columns are working together and your specific requirements, we may be able to consolidate/streamline more.
-
Hi Paul,
Thanks for your help. I have made minor adjustments to the formulas and now all seems to be working.
HEALTH COLUMN
=IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN()), [Index Health]@row = 1), "Yellow", IF(OR(CONTAINS("Gray", DESCENDANTS()), [Index Health]@row = 3), "Gray", IF(OR(CONTAINS("Green", DESCENDANTS()), [Index Health]@row = 2), "Green", IF(OR(CONTAINS("Red", CHILDREN()), [Index Health]@row = 0), "Red", IF([Cancelled?]@row = 1, "Green", "")))))
HEALTH (INDEX COLUMN)
=IF([Cancelled?]@row = 1, "", IF(OR(AND(Start@row = "", [Estimated End Date]@row = "", [% Complete]@row <= 0), AND(Start@row > TODAY(), [% Complete]@row <= 0)), 4, IF([% Complete]@row = 1, 2, IF(OR(AND(ISDATE(Start@row), [Estimated End Date]@row = "", [% Complete]@row > 0), AND(ISDATE(Start@row), [Estimated End Date]@row > TODAY(+3), [% Complete]@row > 0), AND(ISDATE(Start@row), [Estimated End Date]@row >= TODAY(), [Estimated End Date]@row <= TODAY(+3), [% Complete]@row > 0.85)), 3, IF(OR(AND(Start@row < TODAY(), [Estimated End Date]@row = "", [% Complete]@row <= 0), AND([Estimated End Date]@row >= TODAY(), [Estimated End Date]@row <= TODAY(+3), [% Complete]@row < 0.85)), 1, IF(AND([Estimated End Date]@row < TODAY(), [% Complete]@row < 1), 0))))))
STATUS COLUMN
=IF([Cancelled?]@row = 1, "Cancelled", IF([% Complete]@row = 1, "Completed", IF(AND(Start@row = "", [Estimated End Date]@row = "", [% Complete]@row = ""), "Not Started", IF(AND(ISDATE(Start@row), [Estimated End Date]@row >= TODAY(), [% Complete]@row <= 0), "Not Started", IF(AND(ISDATE(Start@row), [Estimated End Date]@row = "", [% Complete]@row <= 0), "Not Started", IF(AND([% Complete]@row > 0, [Estimated End Date]@row >= TODAY()), "In Progress", IF(AND([% Complete]@row > 0, [Estimated End Date]@row = ""), "In Progress", IF([Estimated End Date]@row < TODAY(), "Late"))))))))
Thanks again for your help! If you can think of better ways to streamline this, please let me know as I'm always eager to learn better ways of performing calculations, specially when a sheet start growing over thousands of rows.
-
Heath Column:
I noticed you changed it to "Green" if Cancelled? is checked. Your original formula had it set to blank for this. Was that a change you meant to make? If that is the case, you can nest that requirement inside of the OR function that generates a Green output.
Health Index Column:
I removed all of the portions referencing "[% Complete]@row < 1" because earlier in the formula you already have an output for if [% Complete]@row equals 1. Everything after that in your formula will be automatically under the assumption that the percent complete is NOT equal to 1, and when dealing with percent complete, you shouldn't go over 1. That means all of your references to "[% Complete]@row < 1" are redundant and not needed. Removing them saves key strokes and puts a little less burden on the sheet as it is one less calculation to make.
Status Column:
For the "Not Started" and "In Progress" statuses, I nested each of them within an OR statement which avoided the need for typing out each of those statuses multiple times within a formula. Again... a little less work on the sheet and fewer keystrokes overall but still produces the same result a little more efficiently.
