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

Find more posts tagged with

Formulas

Accepted answers

All comments

Ray Lindstrom

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

Lori Khoury

It worked!!!! Thank you so much. I love the Smartsheet Community. It saves me so much time.

Thank you again .

Lori Khoury

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

Ray Lindstrom

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

Lori Khoury

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.