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

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

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 followup 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
Categories
Check out the Formula Handbook template!