Formula Help
Good Morning All
Wondering if someone could help me add and addition condition to this formula. I tried and keep getting an error back.
I need to add if Program Status Type is NOT blank in the formula below.
If the end date is less, then today and Status @row is not Complete and Program Status Type is not blank then check the flag in the At Risk column.
=IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)
I am trying to update my Control Center blueprint Project Plan template before I create it in Control Center. Any help would be greatly appreciated.
Thanks
Answers
-
Hi @Lori Khoury,
Try this!
=IF(AND([End Date]@row < TODAY(), NOT(ISBLANK([Program Status Type]@row)), NOT(Status@row = "Complete")), 1, 0)
Hope this helps!
BRgds,
-Ray
-
It worked!!!! Thank you so much. I love the Smartsheet Community. It saves me so much time.
Thank you again .
-
Ray
Any chance you could help me with one more. I would like to do the same thing but for the % Remaining Column. I currently have =1 - [% Complete]@row. But I would only like it to calculate for rows that Program Status Type is not blank. I understand how you put the other formula together. I just dont know how to add the condition to a calculating formula.
If you could help me on this one also I would greatly appreciate it.
Thanks
NOT(ISBLANK([Program Status Type]@row))
-
Hi @Lori Khoury,
I believe this is what you need:
=IF(NOT(ISBLANK([Program Status Type]@row)), 1 - [% Complete]@row, "")
This will perform the % Remaining calculation only if the Program Status Type is not blank. Otherwise it inserts a blank string.
All the best,
-Ray
-
Hi Ray -- Yes it works!!! Thank you Thank you so much. I feel bad asking for one more favor, but I am under the gun to start pushing out new SCC blueprint to my organization tomorrow morning. I posted this question about disabling dependencies and using a weighed formula to calculate % complete Disable Dependencies - Use weighed Formula on % Complete — Smartsheet Community but nobody answered me yet. Any chance you could take a see what i am doing wrong? Below is what I posted. (Okay after I roll get through this, I am going to take Smartsheet formula training. I am so impressed with other people who are proficient in this area!!!)
------------------------
Good Evening
I am wondering if something could possibly tell me what is wrong with the following. I need to disable dependencies on my Network Project Plan. I met with the pro desk, and we test using the following formula and helper column. It appeared to be working but I think we might have tested it with dependencies still enabled.
I am using the Percent Complete Helper formula and I created a % New Percent Column and used the =IFERROR(AVGW formula below but once I disabled the dependencies it doesn't roll up to the parent rows a weighed % complete.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!