Formula for summarized health indicators for all rows

MSMITH2022

We decided to use the formula below to auto color the status for each task Now we are trying to come up with a formula that would auto-calculate all of the row's statuses (Green, Red, Yellow) and display an Overall Health color - I assume it would summarize the colors and display that. Is that possible?

=IF(ISBLANK([End Date]@row), "", IF(AND([End Date]@row <= TODAY(), [% Complete]@row < 1), "Red", IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(), [% Complete]@row < 0.25), "Yellow", IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(), [% Complete]@row >= 0.25), "Green", IF([% Complete]@row = 1, "Green", IF(AND([Start Date]@row >= TODAY(), [End Date]@row >= TODAY()), "Green", ""))))))

Find more posts tagged with

Accepted answers

All comments

Paul H

I would add a helper column to Score each color then hide it, then for the Overall Health, use the average values

Score =IF(Health@row = "Red", 0, IF(Health@row = "Yellow", 1, IF(Health@row = "Green", 2)))

Overall Health =IF(AVG(Score:Score) < 0.75, "Red", IF(AVG(Score:Score) < 1.50, "Yellow", IF(AVG(Score:Score) < 2, "Green")))

MSMITH2022

Thank you very much for your help.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.