Formula to alert Red, Green, Yellow Comparing Progress to Expected % Complete
Can someone help me with a formula to automatically make symbols red, green, or yellow based on comparing Progress icon percentage (0%, 25%, 50%, 100%) to the Expected % Complete.
Within 10% variance- Green
10%+ variance- Yellow
20%+ variance- Red
Thank you!
Answers
-
We have to convert the Harvey Balls to % and then do the variance calculation on the percent..
I also used 0-9 ,10-19.. and 20+
Change the < to a <= to make it 0-10, 11-20, and 21+
Also had to account for a divide by zero case..
=IF(ABS(IF([Expected % Complete]@row = 0, 0, (IF(Progress@row = "Full", 1, IF(Progress@row = "Three Quarter", 0.75, IF(Progress@row = "Half", 0.5, IF(Progress@row = "Quarter", 0.25, 0)))) - [Expected % Complete]@row) / [Expected % Complete]@row)) < 0.2, IF(ABS(IF([Expected % Complete]@row = 0, 0, (IF(Progress@row = "Full", 1, IF(Progress@row = "Three Quarter", 0.75, IF(Progress@row = "Half", 0.5, IF(Progress@row = "Quarter", 0.25, 0)))) - [Expected % Complete]@row) / [Expected % Complete]@row)) < 0.1, "Green", "Yellow"), "Red")
Let me know if that is what you want
Aside:
As a project manager, I am noticing that using the Harvey Balls to report % but having an expected completion in decimal completions... You are going to get a lot of red balls if you are using % variance.
If you say the Project is 18% Target complete it will never be Green or Yellow as your choice is only 0% or 25% Actual Completion
% Variance I take as (Actual-Target)/Target so:
(0-0.18)/0.18 = -100% Variance hence RED
(.25-0.18)/0.18 = 34.21% Variance hence RED
Are you sure you want to be using Harvey Balls and "% Variance" ?
or did you mean Error Bars?
ABS(0.18-0) = 0.18 then Yellow (10%-19%)
ABS(0.18-0.25) = 0.07 then Green (0%-9%)
That would result in fewer Reds
=IF(ABS(IF(Progress@row = "Full", 1, IF(Progress@row = "Three Quarter", 0.75, IF(Progress@row = "Half", 0.5, IF(Progress@row = "Quarter", 0.25, 0)))) - [Expected % Complete]@row) < 0.2, IF(ABS(IF(Progress@row = "Full", 1, IF(Progress@row = "Three Quarter", 0.75, IF(Progress@row = "Half", 0.5, IF(Progress@row = "Quarter", 0.25, 0)))) - [Expected % Complete]@row) < 0.1, "Green", "Yellow"), "Red")
% Variance on Left, Error Bars on Right:
Let me know if this helps
-
Thank you for this! This works well unless the capability (project) is ahead of schedule. For example, we have a capability marked 25% progress and expected %0 the error bar % is showing red. Any ideas to resolve that?
-
@bcwilson.ca let me know if you any ideas on my comment above. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!