Basic RAG Calculation formula based off completion % in project schedule

Dillon Friskney
edited 06/17/20 in Formulas and Functions

Hello All,

I would like to know if anyone knows how to set up a basic RAG formula based on completion % and the schedule date. Custom criteria would be as follows:

  • If % Complete = 100, return Blue
  • If it's not scheduled to start yet, return Clear/Grey
  • If past the scheduled finish date and % complete is less than 100%, return Red
  • If % Complete is greater than or equal to - the prorated expected completion (on track for number of days vs. progress), return Green
  • Otherwise, return Amber


Any help with developing a formula like this would be EXTREMELY helpful! Thank you so much for any/all assistance. - Dillon

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    =IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row < 1), "Red", IF([% Complete]@row >= ((TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row)), "Green", "Yellow"))))

    vs

    =IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "", IF(TODAY() > [Finish Date]@row, "Red", IF([% Complete]@row >= (TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row), "Green", "Yellow"))))


    Really there are only two differences.

    First I removed the AND function from the 3rd IF along with the criteria of [% Complete]@row < 1. Since IF statements work from left to right and stop on the first true value, if it makes it this far in the formula then the % Complete does NOT equal 1. Since it is very highly unlikely that your % Complete will ever EXCEED 1, we can assume that to make it to the 3rd IF, then the % Complete must be less than 1 which makes specifying this criteria redundant. Leaving it in will require just a little more work on the back-end which would be noticeable in sheet performance if you are working in a larger sheet.

    So basically they both say the same thing except in the first formula it is specified and in the second it is implied.


    The other difference is that I removed a set of parenthesis that wasn't really necessary.

    ((TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row))

    vs

    (TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row)


    It is very minor and does not affect performance for either variation. I have just found that I personally prefer to cut those out when I can because they can get a little problematic. That is simply a personal preference.




    Both formulas should function exactly the same. That's why I offered a "variation" as opposed to "right vs wrong".

Answers

  • Hello @Werner Gerstacker, thank you SO much for the quick feedback! I am able to get the first iteration to populate Blue for tasks that are completed and Yellow for tasks not completed. How do I add the 2nd iteration into the same fields to layer in the custom formulas? Thanks

  • Hey @Werner Gerstacker,

    Wow this is coming along great! I can get the first three iterations you sent over to display correctly. It's when I add the 4th iteration in that I receive the #unparseable error. Do you see anything incorrect in here that I should be altering? I've been playing around with it to get the first 3 iterations to come through, but this one is giving me some trouble.

    I appreciate all of your help so much! - Dillon

  • Hello @Werner Gerstacker,


    Thank you VERY much for following up throughout, it finally works great!! I have the final formula below for reference:

    =IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row < 1), "Red", IF([% Complete]@row >= ((TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row)), "Green", "Yellow"))))

    Everything is working perfectly and I really appreciate you diligence in working with me through that one!


    Kind Regards,

    Dillon

  • Hello @Paul Newcome, thanks for contributing! I'm not seeing any issues with this formula either! So that is great. I'm not very understanding in what the differences are or why they lead to the same end result. Can you please help explain? Thanks for your help! - Dillon

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    =IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row < 1), "Red", IF([% Complete]@row >= ((TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row)), "Green", "Yellow"))))

    vs

    =IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "", IF(TODAY() > [Finish Date]@row, "Red", IF([% Complete]@row >= (TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row), "Green", "Yellow"))))


    Really there are only two differences.

    First I removed the AND function from the 3rd IF along with the criteria of [% Complete]@row < 1. Since IF statements work from left to right and stop on the first true value, if it makes it this far in the formula then the % Complete does NOT equal 1. Since it is very highly unlikely that your % Complete will ever EXCEED 1, we can assume that to make it to the 3rd IF, then the % Complete must be less than 1 which makes specifying this criteria redundant. Leaving it in will require just a little more work on the back-end which would be noticeable in sheet performance if you are working in a larger sheet.

    So basically they both say the same thing except in the first formula it is specified and in the second it is implied.


    The other difference is that I removed a set of parenthesis that wasn't really necessary.

    ((TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row))

    vs

    (TODAY() - [Scheduled Start]@row) / ([Finish Date]@row - [Scheduled Start]@row)


    It is very minor and does not affect performance for either variation. I have just found that I personally prefer to cut those out when I can because they can get a little problematic. That is simply a personal preference.




    Both formulas should function exactly the same. That's why I offered a "variation" as opposed to "right vs wrong".

  • Thanks @Paul Newcome and @Werner Gerstacker for contributing and making this a great learning opportunity! The speed of communication was outstanding as well :)

  • Werner Gerstacker
    Werner Gerstacker ✭✭✭✭✭

    No problem - happy to do some 'teaching how to fish' 😉

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!