Rolling up Health to the Parent row

Is there a way to roll up health of tasks underneath the parent to reflect an "average" health, if you will?

I have a formula for my children to render health, however, I can't seem to roll up to the parent. Would the same formula work the same in the parent row, if my formula is based on start/finish date and % complete?

My health formula is: =IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF(Status@row = "Not Started", "Gray", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < 0.5, "Red", IF([% Complete]@row < 0.75, "Yellow", "Green")), IF([Finish Date]@row = TODAY(3), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 0.5, "Yellow", "Green")))))))

Find more posts tagged with

Reports

functionality

Accepted answers

Genevieve P.

Hi @Lauren T

I've answered you on the comment you left on this post: https://community.smartsheet.com/discussion/comment/239978#Comment_239978

I'll re-post the same answer here, in case anyone else has the same question.

It looks like you just have some parenthesis in the wrong order - try this:

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF(Status@row = "Not Started", "Gray", IF(AND([Finish Date]@row = TODAY(), [% Complete]@row < 0.5), "Red", IF([% Complete]@row < 0.75, "Yellow", IF(AND([Finish Date]@row = TODAY(7), [% Complete]@row < 0.25), "Red", IF([% Complete]@row < 0.5, "Yellow", "Green"))))))

Keep in mind that the order is important since logic formulas read left-to-right. For example, in your formula you have the rule that if the Percent Complete is 100% OR if the Start Date is in the future, the ball will turn green. Then you have a rule that if the Status is "Not Started" the ball will turn Gray. This means that if a row has the date in the future and it says "Not Started", the Green ball will be the one returned since it's the first rule.

I'll break it out to make sure it says what you want it to:

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green",

If either the % Complete is 100 OR if the Start Date is in the future, return Green

IF(Status@row = "Not Started", "Gray",

If the Status is "Not Started", return Gray.

3 .

IF(AND([Finish Date]@row = TODAY(), [% Complete]@row < 0.5), "Red",

If the Finish Date is Today AND the Percent Complete is less than 50%, return Red. (You can change out the AND for OR, if you prefer).

4 .

IF([% Complete]@row < 0.75, "Yellow",

If the Percent Complete is less than 75%, return Yellow

5 .

IF(AND([Finish Date]@row = TODAY(7), [% Complete]@row < 0.25), "Red",

If the Finish Date is exactly 7 days from now, AND the % Complete is less than 25%, return Red. (You may want to change the = to be <=, less than or equal to, to indicate within the next week instead of exactly 7 days from now).

6 .

IF([% Complete]@row < 0.5, "Yellow",

If it doesn't match any of the criteria above, and the Percent Complete is less than 50%, return Yellow.

"Green"))))))

Finally, if none of the above criteria are met, return Green.

Let me know if you have any questions about how this was built, or how to adjust the criteria.

Cheers,

Genevieve

Genevieve P.

Hi Lauren,

Good catch! You can add in that Red rule into the Red statement:

IF(OR(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [Due Date]@row < TODAY())), "Red",

This will look for your first Red statement OR if the percent complete is both not 100%, and in the past.

=IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray", IF(OR(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [Due Date]@row < TODAY())), "Red", IF(AND([% Complete]@row <= 0.5, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Yellow", IF(AND([% Complete]@row >= 0.75, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Green", "Green"))))

In regards to using this for a different project, the only thing to change then is what you're instructing the [% Complete] column to search for. You can say "not 100" or "not 0" like this: <> 1 or <> 0

The list of different formula operations like this can be found in our Help Center:

https://help.smartsheet.com/articles/2476171

All comments

Lauren T

I am trying to a formula to measure health at the task level. I figured it out, and it worked perfectly, but realized I wanted to add in the IF statement that IF Status = Not Started, Health would be Gray. However, with the addition to the formula below, it is returning "Incorrect Arguement Set". Any ideas on what part of the formula is incorrect? Thank you for any help!

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF(Status@row = "Not Started", "Gray"), IF([Finish Date]@row = TODAY(), IF([% Complete]@row < 0.5, "Red", IF([% Complete]@row < 0.75, "Yellow", "Green")), IF([Finish Date]@row = TODAY(7), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 0.5, "Yellow", "Green")))))

Genevieve P.

Hi @Lauren T

I've answered you on the comment you left on this post: https://community.smartsheet.com/discussion/comment/239978#Comment_239978

I'll re-post the same answer here, in case anyone else has the same question.

It looks like you just have some parenthesis in the wrong order - try this:

I'll break it out to make sure it says what you want it to:

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green",

If either the % Complete is 100 OR if the Start Date is in the future, return Green

IF(Status@row = "Not Started", "Gray",

If the Status is "Not Started", return Gray.

3 .

IF(AND([Finish Date]@row = TODAY(), [% Complete]@row < 0.5), "Red",

If the Finish Date is Today AND the Percent Complete is less than 50%, return Red. (You can change out the AND for OR, if you prefer).

4 .

IF([% Complete]@row < 0.75, "Yellow",

If the Percent Complete is less than 75%, return Yellow

5 .

IF(AND([Finish Date]@row = TODAY(7), [% Complete]@row < 0.25), "Red",

6 .

IF([% Complete]@row < 0.5, "Yellow",

If it doesn't match any of the criteria above, and the Percent Complete is less than 50%, return Yellow.

"Green"))))))

Finally, if none of the above criteria are met, return Green.

Let me know if you have any questions about how this was built, or how to adjust the criteria.

Cheers,

Genevieve

Lauren T

@Genevieve P

Thank you SOOOOO much! And thank you for breaking it down, it makes so much sense to me now that you've broken it down line by line.

Seeing it broken out and what each section means, it looks like I'm not getting exactly what I need. I need the following criteria met, I'm just not sure how to write the formula correctly:

If Status = Not Started AND Start Date is in the future, Grey
If % Complete is equal to or greater than 75% AND "Due Date" is in 1 week, then Green
If % Complete is equal to or less than 50% AND "Due Date" is in 1 week, then Yellow
If % Complete is equal to or less than 25% AND "Due Date" is in 1 week, then Red
If none of the criteria above are met, then Green

I'm basically wanting to set the health of my project based on the due date compared to the % complete, is this is right logic? Thanks so much for your help.

Lauren

Genevieve P.

Hi Lauren,

Since Logic formulas read left-to-right, I've reorganized the order (otherwise the rule for Yellow to look at "less than 50" would include the red ones as well. This is why I've put the Red criteria first).

Try this:

=IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray", IF(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Red", IF(AND([% Complete]@row <= 0.5, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Yellow", IF(AND([% Complete]@row >= 0.75, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Green", "Green"))))

Keep in mind that you have no statement for anything between 50 - 75%... so since your default is Green, anything between that with a due date this week will be Green.

Here is each statement in comparison to (& in the order of) your points above:

If Status = Not Started AND Start Date is in the future, Grey

=IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray",

If % Complete is equal to or greater than 75% AND "Due Date" is in 1 week, then Green

IF(AND([% Complete]@row >= 0.75, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Green",

If % Complete is equal to or less than 50% AND "Due Date" is in 1 week, then Yellow

IF(AND([% Complete]@row <= 0.5, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Yellow",

If % Complete is equal to or less than 25% AND "Due Date" is in 1 week, then Red

IF(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Red",

If none of the criteria above are met, then Green

"Green"))))

Let me know if this works!

Cheers,

Genevieve

Lauren T

@Genevieve P

It works great! The only thing is that is the due date has passed, it doesn't matter the % complete - it remains Green. Is that because of the last bullet point, where if the criteria is not met above, then Green? Would it change if I take it out? Would a "If (due date)@row is passed, "Red" work?

Also, I am trying this on a project where the only % complete numbers will be 100% or 0% - how do I add that logic in? Or will I need to come up with a whole other formula?

Thank you again for your continued help, this is saving me as I'm terrible at formulas!

Genevieve P.

Hi Lauren,

Good catch! You can add in that Red rule into the Red statement:

IF(OR(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [Due Date]@row < TODAY())), "Red",

This will look for your first Red statement OR if the percent complete is both not 100%, and in the past.

The list of different formula operations like this can be found in our Help Center:

https://help.smartsheet.com/articles/2476171

Candace Hebert

Hi, I posted this in the other thread as well.

@Genevieve P

Can you please tell me what column names I need for this to work?

Do I need anything else for this formula to work?

Also, do I paste this formula in the health column?

Thank you!

Candace

Genevieve P.

Hi @Candace Hebert

It may be better to write your own formula with specific rules based on the criteria you want to see... but here are the column names that are used in this formula:

There's a Start Date column

and a Due Date column

and a % Complete column.

This would be pasted into the Health column, or whichever column is returning the RGY balls. This formula accounts for a Gray ball as well.

Let me know if it works for you, or if you want to adjust any of the criteria that Lauren had specified and I'm happy to help! It may also be useful to see a screen capture of your sheet (but please block out any sensitive data).

Cheers,

Genevieve

Candace Hebert

Thanks, @Genevieve P!

I've attached a screenshot of my sheet. I've used the formula you provided but it says UNPARSABLE.

Can you please help?

dashboard.png

Genevieve P.

Hi @Candace Hebert

No problem, happy to help! It looks like you've pasted the formula into the % Complete column. Since we're referencing that column in the formula, we want to actually paste this into a new column... one that is a Symbol type of column (see here) that has the different coloured balls as the output. You could call this column "Health" or "Status". Does that make sense?

Molly Lindblom

Hi @Genevieve P , Your explanation above was a big help. I used the exact formula you provided

The formula works well for most Level 3 (individual task) items although toward the end of my project sheet the anything not started is inexplicably showing as green. This despite showing Not Started and 0% complete.
I have some Level 2 items (tasks rolled up into larger categories like Sales Enablement or Website updates) and Level 1 items (Stage 1, Stage 2, etc.). The Level 1 and 2 items where all level 3 items are 100% are green as expected. The Stage 1 and Stage 2 categories with in-progress or not started Level 3 categories show yellow even though the due date for Level 1 and 2 items are more than seven days away. Any idea why that would happen?

Thanks in advance for your help.

Genevieve P.

Hi @Molly Lindblom

This formula evaluates each row individually, without looking to see if it's a Parent or Grandparent task. It will be dependent on that row's data.

Keep in mind if you're using Project Settings on your sheet, the data in your Level 1 and Level 2 header rows will be rolled up from the child rows using the Parent Rollup Functionality.

This means that the Start Date of these rollup rows will show the earliest Start Date of all its children, and the latest End Date of its children. Therefore, if one of the Child tasks starts either Today or earlier, but one of the other children rows has an End Date in the future, then this row would show Green. Could this be what's happening for your first point?

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green",

If either the % Complete is 100 OR if the Start Date is in the future, return Green.

For your second point, it's hard to identify what might be happening without seeing your sheet set up, hierarchy, and formula. Would you be able to post a screen capture, showing the formula? (Double click on the cell). Please block out any sensitive data from the image!

Cheers,

Genevieve

Irene D

Hi @Genevieve P,

I've re-capped the formula you previously created for Lauren (see below). Please note that I switched out "Due Date" with "End Date" to align with the table headings in my sheet.

If Status = Not Started AND Start Date is in the future, Grey

· =IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray",

If % Complete is equal to or less than 25% AND "End Date" is in 1 week, then Red AND if “End Date” is passed, then red

· IF(OR(AND([% Complete]@row <= 0.25, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [End Date]@row < TODAY())), "Red",

If % Complete is less than 50% AND "End Date" is in 1 week, then Yellow

· IF(AND([% Complete]@row < 0.5, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Yellow",

If % Complete is equal to or greater than 75% AND "End Date" is in 1 week, then Green

· IF(AND([% Complete]@row >= 0.75, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Green",

If none of the criteria above are met, then Green

· "Green"))))

However, since my sheet also contains a "Status" (not started, in progress or complete) column in addition to the "% Complete", I would like some help to make modifications to the formula. Is there a way to reflect that if a start date is in the past and the status is "Not Started", then red? Similarly, if the status is "In Progress" or "Not Started" and the end date is in the past, then red?

Thank you in advance!

Irene

Genevieve P.

Hi @Irene D

No problem! We can include those two status options as an OR statement within the RED section of the formula:

If % Complete is equal to or less than 25% AND "End Date" is in 1 week, then Red

OR if “End Date” is passed and % complete is not 100%, then Red

OR if "End Date" is passed and Status is either "in Progress" or "Not Started", then Red

IF(OR(AND([% Complete]@row <= 0.25, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [End Date]@row < TODAY()), AND(OR(Status@row = "In Progress", Status@row = "Not Started"), [End Date]@row <= TODAY())), "Red",

Does that work for you? Do you have other Status criteria you want to build in to the other colours?

Cheers!

Genevieve

Irene D

Hi @Genevieve P ,

I was hoping to also create a completely different Status criteria for a different sheet that I have.

1) If Status = Not Started AND Start Date is in the future, Grey

2) OR if "Start Date" past up to 5 business days AND Status is "Not Started”, then Yellow

3) OR if “End Date” is in past AND % complete is not 100%, then Red

OR if "End Date" is in past AND Status is either "in Progress" or "Not Started", then Red

OR if "Start Date" is in past AND Status is "Not Started", then Red

4) OR if % Complete is equal to 100% OR Status is “Complete”, then Green

OR if "Start Date" is in past and Status is "In Progress", then Green

Is there any chance you could kindly help me build something based on this criteria?

Thanks!

Irene

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.