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.

Tags:

• ✭✭✭✭✭✭

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")))

• ✭✭✭✭✭✭

=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")))

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

=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")))

• Awesome @Paul Newcome! THANKS SO MUCH!

• @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!