task status based on expected % complete

I am trying to write a formula that will return red, yellow, green, or gray staus symbols based on the following criteria:

  • Gray  - If actual % complete = expected % complete
  • Green - if actual % complete > expected % complete
  • Yellow - if the actual % complete is within 10% below the expected % complete
  • Red - if the actual % complete is more than 10% below the expected % complete

Where "expected % complete" is calculated as follows: =(TODAY() - Start@row) / (Finish@row - Start@row)

Would you be able to help me with the formula? Also please feel free to correct my assumptions if they overlap or are not inclusive.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are going to want something like this...

    =IF([Actual % Complete]@row = [Expected % Complete]@row, "Gray", IF([Actual % Complete]@row > [Expected % Complete]@row, "Green", IF([Actual % Complete]@row = [Expected % Complete]@row - 0.1, "Yellow", "Red")))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    =IF(OR([Actual % Complete]@row = [Expected % Complete]@row, [Start Date]@row > TODAY()), "Gray", IF([Actual % Complete]@row > [Expected % Complete]@row, "Green", IF([Actual % Complete]@row = [Expected % Complete]@row - 0.1, "Yellow", "Red")))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are going to want something like this...

    =IF([Actual % Complete]@row = [Expected % Complete]@row, "Gray", IF([Actual % Complete]@row > [Expected % Complete]@row, "Green", IF([Actual % Complete]@row = [Expected % Complete]@row - 0.1, "Yellow", "Red")))

  • @Paul Newcome this is great thank you are a rockstar! I have one follow-up question. If I wanted one additional criterion:

    If the Expected %complete <=0 show gray not green (ie. task has a start date prior to today and therefore showing a 0 or negative value for Expected % complete)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    =IF(OR([Actual % Complete]@row = [Expected % Complete]@row, [Start Date]@row > TODAY()), "Gray", IF([Actual % Complete]@row > [Expected % Complete]@row, "Green", IF([Actual % Complete]@row = [Expected % Complete]@row - 0.1, "Yellow", "Red")))

  • @Paul Newcome how would you account for #divide by zero errors for milestones in the formula?

  • @Paul Newcome I think the error is a result of the formula I was using in the expected % complete column:

    original formula =ROUND((TODAY() - Start@row) / (Finish@row - Start@row), 2)

    Adjusted for error: =IFERROR(ROUND((TODAY() - Start@row) / (Finish@row - Start@row), 2), "")

    Still it's not exactly what I would hope to see. See the image below:

    I would want calculated status for the milestones - Depending on if the Actual % complete is 0 or 100% taking into consideration the finish date of that milestone and today's date.

    behind schedule (missed) - RED if actual % complete is = 0 and the finish date is today or later

    ahead of schedule - GREEN if actual % complete is = 100 and the finish date is before today

    on time- GRAY if actual % complete is = 100 and the finish date = Today

    Not started - GRAY if actual % complete is = 0 and the finish date is before today.


    Does this make sense?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!