task status based on expected % complete

wodifysoftware

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.

Paul Newcome

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

=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

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

wodifysoftware

@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

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

wodifysoftware

Awesome @Paul Newcome! THANKS SO MUCH!

wodifysoftware

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

wodifysoftware

@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?