Assessing a Project's health based on % complete against % of time passed in activity du

rcollier67 ✭✭
edited 11/21/23 in Formulas and Functions

I am looking how to enter a formula in SmartSheet that works for me in excel. I want to get a 'proactive warning" if a project is not tracking well to when it needs to be completed.

If TODAY() is 60% through the allocated time for an activity but my pecent complete is actually 25% less than 60% , then I get a RED. If I am 25% to 0% less than the 60%, then YELLOW. If my % complete is ahead of TODAY()'s straightline use of allocated time, then GREEN.

Here is what I tried but I get an Error in Smartsheet: I get an UNPARSEABLE error.

=IF(IF(TODAY()>[ECD]@row,100%,(TODAY()-[Start Date]@row)/(ECD]@row-[Start Date]@row))-[% Complete]@row>=25%,"RED",IF(AND(IF(TODAY()>ECD]@row,100%,(TODAY()-[Start Date]@row)/(ECD]@row-[Start Date]@row))-[% Complete]@row<25%,IF(TODAY()>ECD]@row,100%,(TODAY()-[Start Date]@row)/(ECD]@row-[Start Date]@row))-[% Complete]@row>0),"YELLOW",IF(IF(TODAY()>ECD]@row,100%,(TODAY()-[Start Date]@row)/(ECD]@row-[Start Date]@row))-[% Complete]@row<0,"GREEN","GREEN")))


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!