Formula help on RYG Balls for Baseline Variance

hello all

I'm struggling with this because the variance is in Days. I did a basic IF statement where:

if the Status = complete then Green,

and "nested if" if the Variance was < -5 then Red else Yellow.

It just turned complete things Green and everything else yellow, because the variance is in Days (eg. 1d or -2d not a read number)

Ultimate Goal is to put the health of the project on a report then Dashboard for Executive lead team. Schedule Variance is my first thought.

Thanks for any guidance!

Find more posts tagged with

Formulas

Baseline

Accepted answers

All comments

dojones

It's seeing the Variance as text. Create a helper column and use this formula:

=VALUE(LEFT(Variance@row, FIND("d", Variance@row) - 1))

Then base your IF statement on the formula. You could also build the formula into your IF statement to avoid the helper column.

dkariotakis

This is the logic I couldn't figure out how to build in a helper column. Thanks!

dkariotakis

actually, it's not working. it seems the parenthesis aren't adding up. I tried just added a closing paren, but that didn't work thoughts?

dkariotakis

nevermind, it's not the parenthsis. but it's still not working

dojones

Try this.

Health formula is:

=IF(Status@row = "Complete", "Green", IF(Variance@row < -5, "Red", "Yellow"))

Tag me @dojones in response if you want me to see it.

dkariotakis

I have it sorted out @dojones appreciate the feedback! I did end up with a helper column, because I have to re baseline occasionally. Thanks!!

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.