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.

Find more posts tagged with

Formulas

Gantt View

Accepted answers

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

All comments

Paul Newcome

You are going to want something like this...

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

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.