Basic RAG Calculation formula based off completion % in project schedule
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
-
I'd approach this by starting with a simple IF statement and nest additional conditions into it until you have them all covered, i.e.
1st iteration: =IF([% Complete]@row=1,"Blue","Amber")
2nd iteration: =IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "Gray", "Amber"))
etc.
Notes for color-coding:
If you want to use a 'Symbol' column you need to adhere with your results to the options it offers, i.e.
- either RAG & Blue or RAG & Gray, but not RAG & Blue & Gray
- there is no 'Amber', there's only a 'Yellow'
- it's called 'Gray', not 'Grey'
Otherwise you'll have to use a text column for the output and apply conditional formatting for the column to display the color..
-
3rd iteration:
=IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "", IF(AND(TODAY() > [Finish Date]3, [% Complete]3 < 1), "Red", "Yellow")))
4th iteration:
=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]6 - [Scheduled Start]@row)), "Green", "Yellow"))))
Here's the whole thing - I recommend some testing, though.
-
Oh sorry, I just saw there are two items in there where I made an error pasting it in from my sample file:
1) in one of the references - [Scheduled Start@row - there's a square bracket missing
2) [Finish Date]6 needs to be [Finish Date]@row, of course
-
Here is another variation you could try...
=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"))))
-
=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
-
I'd approach this by starting with a simple IF statement and nest additional conditions into it until you have them all covered, i.e.
1st iteration: =IF([% Complete]@row=1,"Blue","Amber")
2nd iteration: =IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "Gray", "Amber"))
etc.
Notes for color-coding:
If you want to use a 'Symbol' column you need to adhere with your results to the options it offers, i.e.
- either RAG & Blue or RAG & Gray, but not RAG & Blue & Gray
- there is no 'Amber', there's only a 'Yellow'
- it's called 'Gray', not 'Grey'
Otherwise you'll have to use a text column for the output and apply conditional formatting for the column to display the color..
-
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
-
3rd iteration:
=IF([% Complete]@row = 1, "Blue", IF([Scheduled Start]@row > TODAY(), "", IF(AND(TODAY() > [Finish Date]3, [% Complete]3 < 1), "Red", "Yellow")))
4th iteration:
=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]6 - [Scheduled Start]@row)), "Green", "Yellow"))))
Here's the whole thing - I recommend some testing, though.
-
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
-
Oh sorry, I just saw there are two items in there where I made an error pasting it in from my sample file:
1) in one of the references - [Scheduled Start@row - there's a square bracket missing
2) [Finish Date]6 needs to be [Finish Date]@row, of course
-
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
-
Here is another variation you could try...
=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"))))
-
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
-
=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 :)
-
Happy to help! 👍️
-
No problem - happy to do some 'teaching how to fish' 😉
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
