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!
Answers
-
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.
-
This is the logic I couldn't figure out how to build in a helper column. Thanks!
-
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?
-
nevermind, it's not the parenthsis. but it's still not working
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!