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

  • bcwilson.ca
    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

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!