Health Indicator Claculation

I Am using the below formular in my Health Indicator but even though my project is on track My overall project indicator is showing Red. What have I done wrong any assistance please :-)

=IF(AND([End Date]@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(1), (TODAY(1) - [Start Date]@row ) / ([End Date]@row - [Start Date]@row ) > [% Complete]@row , [% Complete]@row < 1), "Yellow", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = 0), "Blue", IF(NOT(ISBLANK([% Complete]@row )), IF([% Complete]@row = 0, "Blue", IF([% Complete]@row <= 0.25, "Red", IF([% Complete]@row <= 0.75, "Yellow", "Green"))), IF(Status@row = "Not Started", "Blue", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Completed", "Green", "Blue")))))))

Find more posts tagged with

Accepted answers

Anjanesh Vaidya

Hi @Natasha Adams

This is happening because the first condition in your IF statement is being met, which is why it is returning "Red". In an IF statement, conditions are evaluated in order, and the first true condition takes priority. Once it is fulfilled, the formula stops there and returns that result.

I suggest moving this condition: (=IF(AND([End Date]@row < TODAY(), [% Complete]@row < 1), "Red") to the end of your IF statement. That way, it will only be evaluated after the other conditions, and the formula should then work as expected.

All comments

Anjanesh Vaidya

Hi @Natasha Adams

Paul Newcome

Are you able to provide a screenshot for context?

Natasha Adams

Thanks All Anjanesh was correct I just moved the first IF statement to the end and know it is working correctly

Anjanesh Vaidya

Thanks! Happy to 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.