Formula to alert Red, Green, Yellow Comparing Progress to Expected % Complete

Ashley Johnson

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!

bcwilson.ca

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

Ashley Johnson

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?

Ashley Johnson

@bcwilson.ca let me know if you any ideas on my comment above. Thank you!