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 09 ,1019.. and 20+
Change the < to a <= to make it 010, 1120, 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 (ActualTarget)/Target so:
(00.18)/0.18 = 100% Variance hence RED
(.250.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.180) = 0.18 then Yellow (10%19%)
ABS(0.180.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
Check out the Formula Handbook template!